Обсуждение: specifying table in function args

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.

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.

On Thursday, February 28, 2019, Rob Sargent <robjsargent@gmail.com> wrote:

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.

CREATE TYPE?

David J.

Not yet.

Using just a shell type I from create or replace function public.pvr(seg segment, plus float default 1.0) I get

psql:functions/pvr.sql:19: NOTICE:  argument type segment is only a shell
psql:functions/pvr.sql:19: ERROR:  PL/pgSQL functions cannot accept type segment (same for SQL function)

Using a fleshed-out segment type I get

 \df public.pvr
                                         List of functions
 Schema | Name | Result data type |                  Argument data types                  |  Type 
--------+------+------------------+-------------------------------------------------------+--------
 public | pvr  | double precision | seg public.segment, plus double precision DEFAULT 1.0 | normal
(1 row)

and the schema qualifier on the argument is a killer

camp=# set search_path = base,mm,public;
SET
Time: 0.810 ms
camp=# select pvr(s.*) from mm.segment s limit 5;
ERROR:  function pvr(segment) does not exist
LINE 1: select pvr(s.*) from mm.segment s limit 5;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

On Thu, Feb 28, 2019 at 1:56 PM Rob Sargent <robjsargent@gmail.com> wrote:
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Pretty sure you need to heed the advice to make it work.

select pvr(s::public.segment) from mm.segment AS s limit 5;

David J.