Thread: Trigger function cannot reference field name with capital letter

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

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

On 8/13/2014 9:13 PM, John R Pierce wrote:
>
> SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01
> 00:00:00'

oops.

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

I meant.  I should proof what I write, hah!


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

On 08/13/2014 11:10 PM, Patrick Dung wrote:
> Thanks all for the help.
>
> BTW, letter casing is just a preference.
> Some people liked to use all small caps, some liked to use all big caps.
> I sometimes found that mixed case is more meaningful for the filed (eg.
> serialnumber vs serialNumber)
>
> What is your preference or suggestion?

My preference is lower case with underscores between words. I stay away
from CamelCaps for the reasons Pavel mentioned. Probably the most
important thing is to be consistent in your style.







--
Adrian Klaver
adrian.klaver@aklaver.com

On 14 August 2014 08:10, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:
> Thanks all for the help.
>
> BTW, letter casing is just a preference.
> Some people liked to use all small caps, some liked to use all big caps.
> I sometimes found that mixed case is more meaningful for the filed (eg.
> serialnumber vs serialNumber)
>
> What is your preference or suggestion?

You can use whatever case you like, as long as you never quote your
identifiers so they stay case-insensitive. As long as you stick to
that rule, you can use upper or lower camel caps or all upper or lower
case in your SQL.

The need for quoting identifiers often comes from ORM's that attempt
to preserve case, which is totally unnecessary and causes more trouble
than it has benefits.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.