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 1pbWBQ-0006fs-A8 for pgsql-general@arkaria.postgresql.org; Mon, 13 Mar 2023 00:37:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pbWBO-0004rt-6L for pgsql-general@arkaria.postgresql.org; Mon, 13 Mar 2023 00:36:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pbWBN-0004q5-HO for pgsql-general@lists.postgresql.org; Mon, 13 Mar 2023 00:36:57 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pbWBI-0006Ho-AY for pgsql-general@lists.postgresql.org; Mon, 13 Mar 2023 00:36:56 +0000 Received: by mail-pj1-x1030.google.com with SMTP id y15-20020a17090aa40f00b00237ad8ee3a0so10029510pjp.2 for ; Sun, 12 Mar 2023 17:36:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; t=1678667811; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=Y5x0tFfYggGuHbbfktJShWqqrfoQFXHZiq6a7RKSwus=; b=FMTMGBk14ypzEWPrxIueIt9jjGJWQ8pIOCyleUOwOY46st7PuGxL18UfxQokCQNYDT 96X/3eEGoj71YkkOyeMHexSVzdqg3XU0pqVj8JocOMyYAr7jp5ae5BxUZ1qsS/4N+Fz5 B5ztlM5hMz0RGJPpDJsaRNLYpLc7K25rcFSiE= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678667811; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Y5x0tFfYggGuHbbfktJShWqqrfoQFXHZiq6a7RKSwus=; b=qHfDFw8laxr8e5Qnx5upZV1Ljh3+UaLZ1V4qwTuV9kEpnLo5FaqZ2bEDLLsEOPSiJF FuaR9RWAxFkanIsIDfk3WIo3UQZ/TeuIU9fTOqHpjDIt4Gvwyeij0kBd1ERUns1dHuY2 3w82oa2UXftTU6PAq5oa3p99bC3xSg+SGYIH7V4ZE3BCFkjmvpdNAf2H235cIv0JkQMc b7po2uUYdI81iIG2zTpnHwPxYvmYiVuiThmwzRNbTMpjRAolxqdzBncAqSVRcpM9aZ18 wXtd5IITi9v85BxsY2o+3kIf4yNe7OsoIB6y+R1oLPa64ac/zzxDSr8MW06FeNydjzqA 7siw== X-Gm-Message-State: AO0yUKUMXjZNeg6CACJN/BTnYhOCQAJCnG5TdID3iGj/MHGXz0kXlDmb +BJXPwZ/TBzGtynuxyeBcAC5+Q== X-Google-Smtp-Source: AK7set8lTEjRbalYwItmlXWy3AVLtvH0lltXZefdwo0zBr8ZXzPVVW+lSpR1ycrQyZOvWinoSFnBDw== X-Received: by 2002:a05:6a20:8407:b0:cc:eb3b:56e9 with SMTP id c7-20020a056a20840700b000cceb3b56e9mr44158864pzd.1.1678667810601; Sun, 12 Mar 2023 17:36:50 -0700 (PDT) Received: from smtpclient.apple ([2601:646:101:7fda:9c00:1283:984a:7f23]) by smtp.gmail.com with ESMTPSA id l21-20020a62be15000000b00608dae58695sm3153965pff.209.2023.03.12.17.36.49 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 12 Mar 2023 17:36:50 -0700 (PDT) From: Bryn Llewellyn Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_D4FDA20E-C623-499B-85DC-92D27B2492C5" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.14\)) Subject: Re: Practice advice for use of %type in declaring a subprogram's formal arguments Date: Sun, 12 Mar 2023 17:36:49 -0700 In-Reply-To: Cc: pgsql-general list To: "David G. Johnston" References: <52C06437-4094-494F-B3F8-1148EA3A1C44@yugabyte.com> X-Mailer: Apple Mail (2.3654.120.0.1.14) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D4FDA20E-C623-499B-85DC-92D27B2492C5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > david.g.johnston@gmail.com wrote: >=20 >> bryn@yugabyte.com wrote: >>=20 >> 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? >=20 > If my function is likely to be table-specific I would define its = signature to be: >=20 > function(i_var tablename) >=20 > i.e., they accept a row of said table as the input. >=20 > 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. >=20 > 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). >=20 > You comments do seem, at first glance at least, to refute the = statement in the documentation: >=20 > > 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. >=20 > 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: =E2=80=94 the new master=E2=80=99s unique business key, and some other = facts for it. =E2=80=94 an array of =E2=80=9Cnew within-master details, each with its = within-master business ID and some other facts for it=E2=80=9D 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 =3D pg_catalog, pg_temp language plpgsql as $body$ declare=20 r text not null :=3D ''; begin select k::text into strict r from s.t where v =3D 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 <=3D 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 <=3D 20.0); alter table s.t add column v_new s.num_new; update s.t set v_new =3D 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.) --Apple-Mail=_D4FDA20E-C623-499B-85DC-92D27B2492C5 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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:

=E2=80= =94 the new master=E2=80=99s unique business key, and some other facts = for it.
=E2=80=94 an array of =E2=80=9Cnew = within-master details, each with its within-master business ID and = some other facts for it=E2=80=9D

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 =3D pg_catalog, pg_temp
  language plpgsql
as $body$
declare 
  r text not null :=3D = '';
begin
  select k::text into strict = r from s.t where v =3D 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 <=3D 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 <=3D 20.0);

alter table s.t add column v_new s.num_new;
update s.t set v_new =3D 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.)

= --Apple-Mail=_D4FDA20E-C623-499B-85DC-92D27B2492C5--