Using PG10.7, I have a database per investigator with one or more identical schemata per project. So far I've gotten by defining public functions (as postgres) which reference tables without schema reference. Each project has a role which sets the search_path such that the project specific schema supplies the table definition at function call. This seems to be working quite well.
I've written a function which needs a specific record:
create or replace function public.pvr(seg segment, plus float default 1.0)
returns float as $$
declare
retval float;
begin
select ((1.0*seg.events_equal)+seg.events_greater+plus)/(seg.events_less+seg.events_equal+seg.events_greater+plus) into retval;
return retval;
end;
$$ language plpgsql;
but this fails in one of two ways: either the create function call fails lacking a definition of "segment" or, if I create a public.segment table, create the function, set search_path to include a project's schema then drop public.segment fails because pvr() relies on it.
This is all to avoid (the existing) pv() function which requires all events values as arguments (i.e. much typing). Is there a way through this or must I generate a function per schema in this case? It's easy enough to do, but has a certain odor to it. I suppose I could leave the public.segment table is place (revoke all inserts etc) then let the search_path take over.