Re: Practice advice for use of %type in declaring a subprogram's formal arguments - Mailing list pgsql-general

From Ron
Subject Re: Practice advice for use of %type in declaring a subprogram's formal arguments
Date
Msg-id 8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com
Whole thread Raw
In response to Practice advice for use of %type in declaring a subprogram's formal arguments  (Bryn Llewellyn <bryn@yugabyte.com>)
List pgsql-general
On 3/10/23 15:28, Bryn Llewellyn wrote:
I'm thinking about "language plpgsql" subprograms—but I expect that my question can be generalized to cover "language sql" too.

The payload for "create [or replace]" for a "language plpgsql" subprogram specifies various metadata elements like its qualified name, list of formal arguments and their modes and datatypes, "security invoker/definer" and so on together with the enquoted actual PL/pgSQL source text.

When the DDL succeeds, the PL/pgSQL source text is recorded verbatim in "pg_proc". But the meaning of everything else is parsed out and represented as individual fields in "pg_proc" and other tables like "pg_namespace". This is reflected by the canonical form that "\sf" uses, for example:

create table s.t(k integer primary key, v integer);

create procedure s.p(k_in in t.k%type, v_in t.v%type)
  language plpgsql
as $body$
begin
 /* ... */
end;
$body$

\sf s.p

This is the output:

CREATE OR REPLACE PROCEDURE s.p(k_in integer, v_in integer)
 LANGUAGE plpgsql
AS $procedure$
begin
 /* ... */
end;
$procedure$

This shows that my use of "%type"was consumed at "create" time and then recorded in the catalog as what it translated to. The consequence is that if the table is dropped and re-created thus:

Here's what happens when you use domains.  The first thing I (pun intended) noticed are the two NOTICE messages.  You might have noticed them if your log_min_messages level was high enough.

CREATE DOMAIN
postgres=# create domain d_counting_ints integer check (value > 0);
CREATE DOMAIN
postgres=#
                      ^
postgres=# drop table if exists foo;
NOTICE:  table "foo" does not exist, skipping
DROP TABLE
postgres=# create table foo(pk d_pk, c d_counting_ints);
CREATE TABLE
postgres=#
postgres=# create procedure mypro(p_pk foo.pk%type, p_cnt foo.c%type)
postgres-#     language plpgsql
postgres-# as $body$
postgres$# begin
postgres$#    raise notice '% %', p_pk, p_cnt;
postgres$# end;
postgres$# $body$
postgres-# ;
NOTICE:  type reference foo.pk%TYPE converted to d_pk
NOTICE:  type reference foo.c%TYPE converted to d_counting_ints
CREATE PROCEDURE
postgres=#
postgres=# \sf mypro
CREATE OR REPLACE PROCEDURE public.mypro(p_pk d_pk, p_cnt d_counting_ints)
 LANGUAGE plpgsql
AS $procedure$
begin
   raise notice '% %', p_pk, p_cnt;
end;
$procedure$



--
Born in Arizona, moved to Babylonia.

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Practice advice for use of %type in declaring a subprogram's formal arguments
Next
From: Ben Clements
Date:
Subject: Idea: Add first() and last() aggregate functions to the main release