Hello One of our users is having a problem with a trigger in a system running postgresql 9.3.
The problem is that pl/pgsql does not accept open and close as column names when used in the NEW record in a trigger function. This page: http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html does not say that they are reserved words in postgresql (although they are reserved words in the sql standard) In the other hand, postgres allows to create and update tables with columns named open/close without problems. We think the behavior should be consistent, either it is allow to use them or not, but not like it is today. --------------------------------------------- Test case: --------------------------------------------- CREATE TABLE test_open(id integer,open timestamp); CREATE TABLE test_close(id integer,close timestamp); CREATE TABLE test_close_trigger(id integer,close timestamp); CREATE TABLE test_open_trigger(id integer,open timestamp); CREATE OR REPLACE FUNCTION test_open() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO test_open_trigger (id, open) VALUES (NEW.id, NEW.open); RETURN NEW; END; $function$; CREATE OR REPLACE FUNCTION test_close() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO test_close_trigger (id, close) VALUES (NEW.id, NEW.close); RETURN NEW; END; $function$; # INSERT INTO test_open (id,open) VALUES (1,now()); INSERT 0 1 # INSERT INTO test_close (id,close) VALUES (1,now()); INSERT 0 1 # SELECT * FROM test_open; id | open ----+---------------------------- 1 | 2014-02-06 15:17:52.654977 (1 row) # SELECT * FROM test_close; id | close ----+---------------------------- 1 | 2014-02-06 15:17:53.893911 (1 row) CREATE TRIGGER test_open AFTER INSERT ON test_open FOR EACH ROW EXECUTE PROCEDURE test_open(); CREATE TRIGGER test_close AFTER INSERT ON test_close FOR EACH ROW EXECUTE PROCEDURE test_close(); # INSERT INTO test_open (id,open) VALUES (1,now()); ERROR: record "new" has no field "open" LINE 3: VALUES (NEW.id, NEW.open) ^ QUERY: INSERT INTO public.test_open_trigger (id, open) VALUES (NEW.id, NEW.open) CONTEXT: PL/pgSQL function test_open() line 3 at SQL statement # INSERT INTO test_close (id,close) VALUES (1,now()); ERROR: record "new" has no field "close" LINE 3: VALUES (NEW.id, NEW.close) ^ QUERY: INSERT INTO public.test_close_trigger (id, close) VALUES (NEW.id, NEW.close) CONTEXT: PL/pgSQL function test_close() line 3 at SQL statement --------------------------------------------- Thanks in advance. regards, -- Rafael Martinez Guerrero Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
