On 02/06/2014 06:35 AM, Rafael Martinez Guerrero wrote:
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.
The catch all from here:
http://www.postgresql.org/docs/9.3/interactive/sql-keywords-appendix.html
is:
" As a general rule, if you get spurious parser errors for commands that
contain any of the listed key words as an identifier you should try to
quote the identifier to see if the problem goes away."
Which indeed solves the problem on my end at least:
test=> CREATE OR REPLACE FUNCTION public.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$
;
test=> \d test_open
Table "public.test_open"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | integer |
open | timestamp without time zone |
Triggers:
test_open AFTER INSERT ON test_open FOR EACH ROW EXECUTE PROCEDURE
test_open()
test=> INSERT INTO test_open (id,open) VALUES (1,now());
INSERT 0 1
Thanks in advance.
regards,
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers