| I copied my self-contained testcase, and its output (using PG Version 15.2), at the end. I read the doc section "43.11. PL/pgSQL under the Hood": www.postgresql.org/docs/15/plpgsql-implementation.html Is my mental model, described below, right? 1. With function "s.f()" as presented, the attempt to create it when table "s.t" doesn't yet exist fails with the 42601 syntax error: « invalid type name "s.t.v%type" ». This is expected because some tests must succeed at "create time" else the attempt becomes a no-op. 2. Following creating "s.f()" after creating table "s.t", executing it, and then dropping "s.t", the pg_proc row for "s.f()" remains intact with the original source text. This reflects the fact that the successful "create" didn't record any dependency info so that PG doesn't know what the human observer knows. 3. After "s.t" is re-instated, now with a different data type for "t.v", the SQL query reports the new column data type (and the new content). After all, this is just ordinary query behavior reflecting the current state of the db. However the reported type of the local variable "v_out" is still "text" (as it was at create time) and not "varchar" as it now is. This nominal error reflects the fact that the representation of the to-be-interpreted function, in session memory, was built when "s.f()" was first executed and is now cached. Because there are no dependencies, nothing tells PG to rebuild the representation of the to-be-interpreted function, in session memory. 5. After re-connecting, we have a brand-new session with as yet no cached info. Therefore, the representation of the to-be-interpreted function must be rebuilt when it's first referenced. And now, it sees table "s.t" with a "varchar" column. 6. All this leads to rather obvious practice advice: DDLs on objects that an application uses (at least when the app's database artifacts include PL/pgSQL subprograms) are unsafe while the app is in use. You must stop all client-sessions before doing such DDLs and re-start them only when all DDLs are done successfully. ________________________________________________________________________________ -- Connect as an ordinary user to a convenient database. \c d0 d0$u0 prepare f_prosrc as select prosrc from pg_proc p inner join pg_namespace n on p.pronamespace = n.oid inner join pg_roles r on p.proowner = r.oid where p.proname = 'f' and n.nspname = 's' and r.rolname = $1; create schema s; create table s.t(k serial primary key, v text); insert into s.t(v) values ('cat-1'), ('dog-1'), ('mouse-1'); create function s.f(k_in in int) returns text security definer set search_path = pg_catalog, pg_temp language plpgsql as $body$ declare v_type text not null := ''; v_out s.t.v%type not null := ''; begin select pg_typeof(t.v)::text, t.v into strict v_type, v_out from s.t where t.k = k_in; return 'pg_typeof(t.v): ' ||v_type ||' / '|| 'pg_typeof(v_out): '||pg_typeof(v_out)::text ||' / '|| 'value: ' ||v_out; end; $body$; select '----------'; select s.f(1); drop table s.t cascade; select '----------'; execute f_prosrc('d0$u0'); create table s.t(k serial primary key, v varchar(10)); insert into s.t(v) values ('cat-2'), ('dog-2'), ('mouse-2'); -- "s.f()" still reports "text" for "pg_typeof(v_out)". select '----------'; select s.f(1); \c d0 d0$u0 -- Only now have we lost the cached result of "compiling" the function "s.f()". -- Now reports "character varying” for "pg_typeof(v_out)". select '----------'; select s.f(1); RESULTS (using “\t on” mode) ---------- pg_typeof(t.v): text / pg_typeof(v_out): text / value: cat-1 ---------- < The expected "language plpgsql" source text — verbatim as it was entered, including "%type" (untranslated). > ---------- pg_typeof(t.v): character varying / pg_typeof(v_out): text / value: cat-2 ---------- pg_typeof(t.v): character varying / pg_typeof(v_out): character varying / value: cat-2 |
