Forwarding this to the list; Note the potential bug found at the end. My actual follow-on reply notes the lack of documentation regarding the composite cache-checking behavior (relative to the non-composite situation)
---------- Forwarded message ---------
From: David G. Johnston <david.g.johnston@xxxxxxxxx>
Date: Tue, Mar 7, 2023 at 8:24 PM
Subject: Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses
To: Bryn Llewellyn <bryn@xxxxxxxxxxxx>
From: David G. Johnston <david.g.johnston@xxxxxxxxx>
Date: Tue, Mar 7, 2023 at 8:24 PM
Subject: Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses
To: Bryn Llewellyn <bryn@xxxxxxxxxxxx>
On Tue, Mar 7, 2023 at 7:54 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
(1,cat)
Now, still in the same session:
alter table s.t add c2 text;
update s.t set c2 = 'dog' where k = 1;
select s.f(1);
This is the new result. It surprised me:
(1,cat,dog)
This is what I expected actually, though I can't point to exactly why.
Where can I read what I need in order to understand the difference here, using %rowtype, and in the first test that I posted, using %type?
I'm not certain there should be. Given the presence of the bug below and general infrequency of this scenario I wouldn't be totally surprised there is a bug here as well.
Why is the meaning of %type frozen at "create" time
Nothing in the body of a pl/pgsql routine is frozen at "create time". At the earliest, it freezes at first execution in a session.
Why don't I get a runtime error telling me that I have more "select list" items than "into" targets?
That would be a bug so far as I can tell.
postgres=# do $$declare c1 text; c2 text; begin select '1','2','3' into c1, c2; end;$$;
DO> If a row variable or a variable list is used as target, the command's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs.
David J.
