From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Practice advice for use of %type in declaring a subprogram's formal arguments |
Date: | 2023-03-10 21:28:21 |
Message-ID: | 52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
drop table s.t;
create table s.t(k integer primary key, v text);
the metadata is not changed in sympathy and so "\sf" shows the same as before. This means that I have to find my original DDL script and re-run it—albeit without making any changes to its text.
In other words, the benefit of using "%type" for the declaration of a formal argument is less than using it for the declaration of a local variable.
Do you (all) think that, notwithstanding this, it's a good plan to use "%type" for the declaration of a formal argument just for the reason that it documents your intention explicitly?
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-03-10 21:53:48 | Re: Practice advice for use of %type in declaring a subprogram's formal arguments |
Previous Message | David G. Johnston | 2023-03-10 20:46:48 | Re: select (17, 42)::s.t2 into... fails with "invalid input syntax" |