On 06/12/2015 09:24 AM, Manuel Kniep wrote:
using a row type variable in a trigger function results in an error after changing the table structure running the following sequence of statements (https://gist.github.com/rapimo/accac676f7c8e3557a4d) CREATE TABLE foo( id integer ); CREATE FUNCTION foo_trigger() RETURNS trigger LANGUAGE plpgsql AS $_$ DECLARE r foo%rowtype; BEGIN SELECT NEW.* INTO r; RETURN r; END; $_$; CREATE TRIGGER foo AFTER INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE foo_trigger(); INSERT INTO foo VALUES(1); ALTER TABLE foo ADD COLUMN flag boolean; INSERT INTO foo VALUES(2); results in an error: ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (1) does not match expected column count (2). CONTEXT: PL/pgSQL function foo_trigger() during function exit for the last statement. For me it seems like the variable is cached for the open session after the first insert. Database sessions that didn’t issue an insert on the old structure don’t have that problem. I could reproduce this behavior in postgres 9.4.3, 9.4.1 and 9.4.0
See here: http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN and here: http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
regards Manuel Kniep
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
