Re: Trigger function cannot reference field name with capital letter - Mailing list pgsql-general

On 08/13/2014 08:52 PM, Patrick Dung wrote:
> Hello PGSQL users,
>
> I have a field called postTimestamp.
> The trigger function could not reference it.
> When I change my field to post_timestamp. I can reference it from the
> tigger function.
> Version is 9.3.5. Any comment?
>
> < 2014-08-14 00:23:32.717 HKT >ERROR:  post "new" has no field
> "posttimestamp"

The clue is above. Postgres folds unquoted mixed case to lower case by
default, so it is looking for posttimestamp. If you want to preserve the
mixed case, quote the field name "postTimestamp".

> < 2014-08-14 00:23:32.717 HKT >CONTEXT:  SQL statement "SELECT * from tbl1
>          where NEW.posttimestamp > "2014-01-01 00:00:00" )"
>          PL/pgSQL function test_trigger() line 9 at assignment
> < 2014-08-14 00:23:32.717 HKT >STATEMENT:  INSERT INTO
> public.tbl1("vendor", url, "postTimestamp", product, "type", "itemID")
> VALUES ('vendor1'::text, 'http://example.org'::text, '2014-01-01
> 01:01:01'::timestamp without time zone, 'product1'::text, 'food'::text,
> '1'::bigint)
> < 2014-08-14 00:32:39.708 HKT >ERROR:  syntax error at or near "SELECT"
> at character 314
>
> Thanks and regards,
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com