specifying table in function args

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: specifying table in function args
Date: 2019-02-28 19:10:21
Message-ID: be24ead6-fee6-8bbb-3407-23a5538588ba@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Perumal Raj 2019-02-28 19:21:37 Re: Question about pg_upgrade from 9.2 to X.X
Previous Message Sergei Kornilov 2019-02-28 19:04:15 Re: Question about pg_upgrade from 9.2 to X.X