1

My database is structured with a schema per application user. In each schema there is an identical table named "entries" with the exact same DDL. I also have a common schema that hosts some functions that operate on those "entries" tables, and during execution the search path defines which schema's entries table the function operates on. I had a problem defining those functions before i create any user schema because they reference the non yet created tables in the user schemata. That problem was solved using set check_function_bodies = off; But now I have a similar problem with a function that references those tables as an inout parameter. I cannot create that function because a parameter that it accepts is of type "entries" and I get an error of "type entries does not exist". set check_function_bodies = off does not solve this problem. Is there a way to solve this problem? The function signature is

create or replace function common.reconcile_user_entry(inout e entries) returns void

I really dont want to define this function in each user schema as it will always be the same, and i dont want to have to recompile it in every schema when I need to make a change.

Paralife
  • 6,116
  • 8
  • 38
  • 64

3 Answers3

2

You could use inheritance for that.

Define a table common.entries just like all other entries tables and change the other entries tables to inherit from it:

ALTER TABLE schema1.entries INHERIT common.entries;

Then define the function to have common.entries as parameter or return type, and you can use it with any of the inherited tables.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
2

I think you could use table inheritance.

You would have to create a "parent" table in common schema for every table you have in users schema. Then inherit users tables from them. Use "parent" table names when defining function parameters. See example:

CREATE TABLE parent(id integer);

CREATE TABLE child() INHERITS (parent);
INSERT INTO child(id) VALUES (1);

CREATE FUNCTION get_id(IN prm_row parent) RETURNS integer
LANGUAGE SQL
AS $$
  SELECT prm_row.id;
$$;

SELECT get_id((SELECT (child) FROM child WHERE id = 1));

Note, that parent table will containt the records from all the child tables, so you should use permission to prevent users accessing the parent tables.

Julius Tuskenis
  • 1,323
  • 8
  • 13
0

Instead of going with inheritance I ended up solving it by circumventing the problem like this:

Instead of defining the function signature as

create or replace function common.reconcile_user_entry(inout e entries) returns void

I defined it as

create or replace function common.reconcile_user_entry(inout r record) returns void

and in the function code I declare a variable e entries; and then assign the record into the variable:

declare 
 e entries;
begin
 select into e r.*
...

Regarding the inheritance solution, it works, but I found the above solution much simpler. I know there must be caveats and hidden problems with my solution, if someone can point, please do.

Paralife
  • 6,116
  • 8
  • 38
  • 64