Fwd: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

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(dot)g(dot)johnston(at)gmail(dot)com>
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(at)yugabyte(dot)com>

On Tue, Mar 7, 2023 at 7:54 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> 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.

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

David J.