open and close columns in the NEW record not allowed - Mailing list pgsql-hackers

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 triggerLANGUAGE plpgsql
AS $function$
BEGININSERT INTO test_open_trigger (id, open)VALUES (NEW.id, NEW.open);RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION test_close()RETURNS triggerLANGUAGE plpgsql
AS $function$
BEGININSERT 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/