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.
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.