See noddy example below (v14.6).
Presumably this is a result of procedure cacheing as per docs. The EXECUTE
plan is being prepared fresh (again as expected from the docs and per the error
message) but is the input argument type of NEW.x for the format() call still
cached? Is altering a table column used in a trigger function something that
can be detected and the cache then invalidated? Also "parameter 14" - is there
a way to correlate back from the error message what that paramater acually
corresponds to for the user?
Tim
=# create table a ( x text );
CREATE TABLE
=# create function a_t () returns trigger language plpgsql as $$ begin execute format('select least(%L)', NEW.x); return null; end; $$;
CREATE FUNCTION
=# create trigger a_i after insert on a for each row execute function a_t();
CREATE TRIGGER
=# insert into a values ( '1' );
INSERT 0 1
=# alter table a alter column x type integer using x::integer;
ALTER TABLE
=# insert into a values ( 1 );
ERROR: type of parameter 14 (integer) does not match that when preparing the plan (text)
CONTEXT: PL/pgSQL function a_t() line 1 at EXECUTE
=# create or replace function a_t () returns trigger language plpgsql as $$ begin execute format('select least(%L)', NEW.x); return null; end; $$;
CREATE FUNCTION
=# insert into a values ( 1 );
INSERT 0 1
The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.
