From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Trigger function cannot reference field name with capital letter |
Date: | 2014-08-14 04:13:35 |
Message-ID: | 53EC376F.4020406@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2014-08-14 04:18:11 | Re: Trigger function cannot reference field name with capital letter |
Previous Message | Adrian Klaver | 2014-08-14 04:07:04 | Re: Trigger function cannot reference field name with capital letter |