On 2/28/19 12:27 PM, David G. Johnston wrote:
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.