The following bug has been logged online: Bug reference: 5503 Logged by: Maksym Boguk Email address: maxim.bo...@gmail.com PostgreSQL version: 8.4.3 Operating system: Linux 2.6.18-164 Description: error in trigger function with dropped columns Details:
This bug hard to describe. But in general if a table contained dropped columns you cannot use return record variable in trigger function. Because you get error like: 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 (3). Test case: postgres=# CREATE TABLE test (f1 text, f2 text, f3 text); CREATE TABLE postgres=# insert into test values (1,2,3); INSERT 0 1 CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$ DECLARE _row record; BEGIN RAISE NOTICE 'NEW record = %', NEW; SELECT * INTO _row FROM test limit 1; RAISE NOTICE '_row record = %', _row; RETURN _row; END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# CREATE TRIGGER test_trigger before insert on test for each row EXECUTE PROCEDURE test_function(); CREATE TRIGGER postgres=# insert into test values (1,2,3); NOTICE: NEW record = (1,2,3) NOTICE: _row record = (1,2,3) INSERT 0 1 Ok until now all looks good... now lets drop one column from test: postgres=# ALTER TABLE test drop column f3; ALTER TABLE postgres=# insert into test values (1,2); NOTICE: NEW record = (1,2) NOTICE: _row record = (1,2) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (2) does not match expected column count (3). CONTEXT: PL/pgSQL function "test_function" during function exit OOPS! Recreating function doesn't help. Drop/create trigger again doesn't help too: postgres=# DROP TRIGGER test_trigger on test; DROP TRIGGER postgres=# CREATE TRIGGER test_trigger before insert on test for each row EXECUTE PROCEDURE test_function(); CREATE TRIGGER postgres=# insert into test values (1,2); NOTICE: NEW record = (1,2) NOTICE: _row record = (1,2) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (2) does not match expected column count (3). CONTEXT: PL/pgSQL function "test_function" during function exit If I drop one more column I start getting next error: postgres=# ALTER TABLE test drop column f2; ALTER TABLE postgres=# insert into test values (1); NOTICE: NEW record = (1) NOTICE: _row record = (1) 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 (3). CONTEXT: PL/pgSQL function "test_function" during function exit In the same defining _row test%ROWTYPE; producing no errors in both cases. Thank you very much for your attention. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
