Re: Practice advice for use of %type in declaring a subprogram's formal arguments

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx 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$;

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".

-- 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);

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.)