Re: specifying table in function args

On 2/28/19 12:27 PM, David G. Johnston wrote:
> On Thursday, February 28, 2019, Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)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.