The following bug has been logged on the website:
Bug reference: 15203
Logged by: ಠ_ಠ
Email address: easteregg@verfriemelt.org
PostgreSQL version: 10.4
Operating system: Debian Sid x64
Description:
i created a table with two integer columns and created a triggerfunction to
pass inserted data to a third function to use this data as an input
parameter based on the table.
if i alter the table after function creation, the function becomes not aware
of newly created columns.
strangly enough, after i disconnect and reconnect, the function works as
expected.
i think, the function with the parameter typ which is defined through the
table, should be notified, if the typ - or the table - changes.
i have a gist which contains an example:
https://gist.github.com/verfriemelt-dot-org/76c7de20bde461aef99a7e38a8ae571a
or below:
DROP TABLE IF EXISTS test CASCADE;
DROP FUNCTION IF EXISTS test2;
CREATE TABLE test (
a INTEGER NOT NULL,
b INTEGER NOT NULL
);
CREATE OR REPLACE FUNCTION test2() RETURNS TRIGGER AS $$
BEGIN
PERFORM test3(new);
END $$ LANGUAGE plpgsql;
CREATE TRIGGER test AFTER INSERT OR UPDATE
ON test FOR EACH ROW
EXECUTE PROCEDURE test2();
CREATE OR REPLACE FUNCTION test3(IN src test) RETURNS VOID AS $$
BEGIN
RAISE EXCEPTION 'expected c to be 1: %',src.c;
END $$ LANGUAGE plpgsql;
ALTER TABLE test ADD COLUMN c INTEGER NOT NULL;
-- insert in same session yields an error, that there is no field c
-- testdb=# INSERT INTO test (a,b,c) VALUES (1,1,1);
-- ERROR: record "src" has no field "c"
INSERT INTO test (a,b,c) VALUES (1,1,1);
-- after reconnect to db
INSERT INTO test (a,b,c) VALUES (2,2,2);
-- testdb=# INSERT INTO test (a,b,c) VALUES (1,1,1);
-- ERROR: expected c to be 1: 2