Re: open and close columns in the NEW record not allowed

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(dot)klaver(at)gmail(dot)com