Thread: Practice advice for use of %type in declaring a subprogram's formal arguments
Practice advice for use of %type in declaring a subprogram's formal arguments
From
Bryn Llewellyn
Date:
I'm thinking about "language plpgsql" subprograms—but I expect that my question can be generalized to cover "language sql" too.
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
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$
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?
Re: Practice advice for use of %type in declaring a subprogram's formal arguments
From
"David G. Johnston"
Date:
On Fri, Mar 10, 2023 at 2:28 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
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?
If my function is likely to be table-specific I would define its signature to be:
function(i_var tablename)
i.e., they accept a row of said table as the input.
As most functions don't, and likely shouldn't, care from what source their arguments come from, but instead perform an operation based upon the data types of the inputs, using %type is not something I've ever really had occasion to use.
Given that the source of record loses that metadata anyway that would be further reason why I would just pretend the %type syntax doesn't exist. I suspect that on rare occasion its use could be beneficial, and maybe if I remember it exists while writing one of those use cases up I'll use it, but there doesn't seem to be much downside to just using concrete types everywhere. Code comments can handle intent well enough here, as opposed to some, frankly, esoteric feature/syntax (which seems not all that SQL-ish but I don't see where it is specifically noted as our extension).
You comments do seem, at first glance at least, to refute the statement in the documentation:
> The type of a column is referenced by writing table_name.column_name%TYPE. Using this feature can sometimes help make a function independent of changes to the definition of a table.
I mean, while it is indeed "independent of changes to the definition of a table" so does simply writing "text"...the %type syntax seems like it should follow the changes of the definition of a table...
David J.
On 3/10/23 15:28, Bryn Llewellyn wrote:
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$
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.pThis 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.
Born in Arizona, moved to Babylonia.
Re: Practice advice for use of %type in declaring a subprogram's formal arguments
From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:bryn@yugabyte.com wrote:
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?
If my function is likely to be table-specific I would define its signature to be:
function(i_var tablename)
i.e., they accept a row of said table as the input.
As most functions don't, and likely shouldn't, care from what source their arguments come from, but instead perform an operation based upon the data types of the inputs, using %type is not something I've ever really had occasion to use.
Given that the source of record loses that metadata anyway that would be further reason why I would just pretend the %type syntax doesn't exist. I suspect that on rare occasion its use could be beneficial, and maybe if I remember it exists while writing one of those use cases up I'll use it, but there doesn't seem to be much downside to just using concrete types everywhere. Code comments can handle intent well enough here, as opposed to some, frankly, esoteric feature/syntax (which seems not all that SQL-ish but I don't see where it is specifically noted as our extension).
You comments do seem, at first glance at least, to refute the statement in the documentation:
> The type of a column is referenced by writing table_name.column_name%TYPE. Using this feature can sometimes help make a function independent of changes to the definition of a table.
I mean, while it is indeed "independent of changes to the definition of a table" so does simply writing "text"...the %type syntax seems like it should follow the changes of the definition of a table...
Suppose you have a masters-and-details table pair where each table uses an autogenerated PK. A masters row, following the text book, will also have a unique business key. Similarly, and in one plausible design, a details row will have a unique business identifier within the context of its masters row so that its unique business key will have two parts. Now you want to insert a new master row and a few details for it. This is a fine use case for a PL/pgSQL procedure with these input formal arguments:
— the new master’s unique business key, and some other facts for it.
— an array of “new within-master details, each with its within-master business ID and some other facts for it”
The code writes itself: "insert into masters values... returning PK into m_pk" followed by "insert into details... select... unnest(details_arr_in)". This, at least on the face of it, would be an obvious candidate for using %type. Both for the various input arguments and for the local variable, "m_pk", for the masters PK that gets inserted into the details table. Except for the fact that it doesn't live up to its promise. David said "the %type syntax seems like it should follow the changes of the definition of a table". I agree. But it doesn't. And I don't suppose that it ever will.
However, the requirement for "single point of definition" (hereinafter SPOD) is broader than just PL/pgsql local variables and ideally (but not usably) subprogram formal arguments. For example, route distance between two points on the surface of the earth, with agreed units, scale, precision, and the requirement to fall between zero and a sensible upper limit, is a good candidate for SPOD-ification. A domain gives you exaclty the mechanism you need.
I did this little test:
-- in cr-function.sql
create function s.f(v_in in s.num)
returns text
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
r text not null := '';
begin
select k::text into strict r from s.t where v = v_in;
return r;
end;
$body$;
returns text
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
r text not null := '';
begin
select k::text into strict r from s.t where v = v_in;
return r;
end;
$body$;
and
-- in test.sql
-- Deliberate poor definition of domain "s.num" (upper bound is too small).
create domain s.num as numeric constraint num_ok check(value > 0.0 and value <= 10.0);
create table s.t(k integer primary key, v s.num);
insert into s.t(k, v) values (1, 5);
\ir cr-function.sql
select s.f(5.0);
-- Improved definition of domain "s.num".
create domain s.num as numeric constraint num_ok check(value > 0.0 and value <= 10.0);
create table s.t(k integer primary key, v s.num);
insert into s.t(k, v) values (1, 5);
\ir cr-function.sql
select s.f(5.0);
-- Improved definition of domain "s.num".
-- Using "create" rather than "alter" for maximum generality.
-- No might want to change the base type, too, in a different use case.
create domain s.num_new as numeric constraint num_ok check(value > 0.0 and value <= 20.0);
alter table s.t add column v_new s.num_new;
update s.t set v_new = v::s.num_new;
alter table s.t rename column v to v_old;
alter table s.t rename column v_new to v;
alter table s.t drop column v_old;
drop domain s.num cascade; --> drop cascades to function s.f(s.num)
alter domain s.num_new rename to num;
insert into s.t(k, v) values (2, 14.5);
\ir cr-function.sql
select s.f(14.5);
create domain s.num_new as numeric constraint num_ok check(value > 0.0 and value <= 20.0);
alter table s.t add column v_new s.num_new;
update s.t set v_new = v::s.num_new;
alter table s.t rename column v to v_old;
alter table s.t rename column v_new to v;
alter table s.t drop column v_old;
drop domain s.num cascade; --> drop cascades to function s.f(s.num)
alter domain s.num_new rename to num;
insert into s.t(k, v) values (2, 14.5);
\ir cr-function.sql
select s.f(14.5);
Using the domain, and everything that this implies when you want to change its definition, means that you're forced to accept using "delete domain... cascade" which drops function "s.f()" in its train. In other words, you can't forget to re-create it. And this brings correctness.
This all seems to be very satisfactory. (It doesn't change my thinking about stopping all ordinary client-sessions before doing the patching.)