Re: specifying table in function args

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.