Search Postgresql Archives

Re: Trigger function cannot reference field name with capital letter

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 8/13/2014 8:52 PM, Patrick Dung wrote:

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"
< 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


field and talbe names with mixed case have to be "Quoted".      string constants, on the other hand, are are in single 'quotes'.

try...

SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01 00:00:00'


< 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

those two error logs have different timestamps, I don't believe that error is on that statement.

there's a whole lot of unnecessary typecasting in that insert, however.

INSERT INTO public.tbl1("vendor", url, "postTimestamp", product, "type", "itemID")
    VALUES ('vendor1', 'http://example.org', timestamp '2014-01-01 01:01:01', 'product1', 'food', 1)

would suffice nicely.




-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux