Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1palYl-0005Sn-Ft for pgsql-general@arkaria.postgresql.org; Fri, 10 Mar 2023 22:49:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1palYk-0001Gy-7I for pgsql-general@arkaria.postgresql.org; Fri, 10 Mar 2023 22:49:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1palYj-0001Gj-Pe for pgsql-general@lists.postgresql.org; Fri, 10 Mar 2023 22:49:57 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1palYh-0004rJ-61 for pgsql-general@lists.postgresql.org; Fri, 10 Mar 2023 22:49:57 +0000 Received: by mail-qt1-x82d.google.com with SMTP id y10so7547929qtj.2 for ; Fri, 10 Mar 2023 14:49:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1678488593; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=8ciAewqhvjlabbhwgO70YLapOOpDhfuOBg8yMkgBot4=; b=ib4+RSRDZDhHK5Kx+2stfmdX8WjqWxymendPoaXlp72Gzn59KILLHbcl4YkyQcWgYQ 00wPhL/ucVp4OrxT7+A2AVZhXmrcnbDqQOmKCyoDCuUitiP/IYWk5yDASMuIrhYsluRw 3KjsLJeUcMUmDx0j8gSwvJjCopY8DpAjIM334gWal4YoFnCN1RLrmhqW6UZwTzrOqxmp x6VMPVzd/RKA5jxel0cvPfaLsQLMWtryD76kEogW9bWQQstvl4IEfFUkDTb8WZlpiKCy CEHQuWLVPaS2JXWU1N8YLXiLcrfT17jED9G3H7GGAkbU9gnbkP3JYNRsu+An1Jfw22Zm ptmA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678488593; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=8ciAewqhvjlabbhwgO70YLapOOpDhfuOBg8yMkgBot4=; b=cagFvE7rvMJ8hZIRxNDMsk/yuunNCNoypw4+5qxXu6J05X8/eLGZ0ghXMT9Mqf21+H tZaIKWMFzn6tHPe1Ph+nUAGyRslOTyliAcLHqWSdJtTw2VuH9q+InS1E3gk/6jtMQe6e tfeWGauHnRq1nH3cvc4q/zYIiflKcqtXGvmY91UAh6rkZoRXtHA+8LO1qP5aODA/VX/Z a4bDra7V2XA2scSqqr8tEpt0G3zMaYcHhXnq43jgh+Wa+7tokiWFBDBsQLeud9m8HjKz PYC1Nc/qD0F6A3K+dQQJ8aBdBTnS/dzHgHDbWwOLW3Q4b2oHJ1FRtT/OUmSBXSCtDlyJ UrnQ== X-Gm-Message-State: AO0yUKVITqbaNQcWlclxFhDLonGpJDLtfUL4qe7S1V9VCyw9QgqpvIkH /1nvoB5QN5+nZWUGFzzzVa29UtfuqZY= X-Google-Smtp-Source: AK7set8ufp8yj5oA5GHCjUft7ioiDqGwAc51/vIwF1hqGQ4JP7nrtnPqtVmo8NVh2+6tqBTSZAHTLg== X-Received: by 2002:ac8:5794:0:b0:3b9:bc8c:c202 with SMTP id v20-20020ac85794000000b003b9bc8cc202mr7618710qta.13.1678488593033; Fri, 10 Mar 2023 14:49:53 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id t65-20020ae9df44000000b007426ec97253sm622480qkf.111.2023.03.10.14.49.52 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 10 Mar 2023 14:49:52 -0800 (PST) Content-Type: multipart/alternative; boundary="------------CVrnYQ9MuOz0e1VfaSmzX6tJ" Message-ID: <8b4850d2-5f97-f0ff-5288-b9861d9e7f72@gmail.com> Date: Fri, 10 Mar 2023 16:49:52 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Practice advice for use of %type in declaring a subprogram's formal arguments Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com> From: Ron In-Reply-To: <52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------CVrnYQ9MuOz0e1VfaSmzX6tJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------CVrnYQ9MuOz0e1VfaSmzX6tJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------CVrnYQ9MuOz0e1VfaSmzX6tJ--