pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Practice advice for use of %type in declaring a subprogram's formal arguments
Date: Fri, 10 Mar 2023 16:49:52 -0600
Message-ID: <8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com> (raw)
In-Reply-To: <52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com>
References: <52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com>

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.

view thread (4+ messages)  latest in thread

Message-ID: <8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com>
Permalink:  ../8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com/
Also on:    postgresql.org/message-id/8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Practice advice for use of %type in declaring a subprogram's formal arguments
  In-Reply-To: <8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com>
  References: <52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com> <8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox