How to check if a field exists in NEW in trigger

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

How to check if a field exists in NEW in trigger

Igal @ Lucee.org

I have the following statement in a trigger:

    new.email = lower(new.email);

When I try to update a record without setting the email column however, I get an error:

SQL Error [42703]: ERROR: record "new" has no field "email"
  Where: SQL statement "SELECT lower(new.email)"
PL/pgSQL function on_record_modified() line 26 at assignment

I have seen some hacks suggesting TRY/CATCH or converting to a JSON and checking if the field exists, but I would think that there's a better way to check if the field is in the NEW record, no?

Any ideas?  Thanks!

Igal Sapir
Lucee Core Developer
Lucee.org

Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Adrian Klaver-4
On 8/4/19 3:52 PM, Igal @ Lucee.org wrote:
> I have the following statement in a trigger:
>
>      new.email = lower(new.email);
>
> When I try to update a record without setting the email column however,

Do you mean:

1) There is no actual email column?

2) There is an email column but no value for it?



> I get an error:
>
> SQL Error [42703]: ERROR: record "new" has no field "email"
>    Where: SQL statement "SELECT lower(new.email)"
> PL/pgSQL function on_record_modified() line 26 at assignment

Is:

on_record_modified()

a generic function that will be applied to many tables with differing
schema?

>
> I have seen some hacks suggesting TRY/CATCH or converting to a JSON and
> checking if the field exists, but I would think that there's a better
> way to check if the field is in the NEW record, no?

Use

TG_RELID

and look up the columns in:

https://www.postgresql.org/docs/11/catalog-pg-attribute.html

Or use the columns information_schema:

https://www.postgresql.org/docs/11/infoschema-columns.html

>
> Any ideas?  Thanks!
>
> Igal Sapir
> Lucee Core Developer
> Lucee.org <http://lucee.org/>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Thomas Kellerer
In reply to this post by Igal @ Lucee.org
Igal @ Lucee.org schrieb am 05.08.2019 um 00:52:

> I have the following statement in a trigger:
>
>     new.email = lower(new.email);
>
> When I try to update a record without setting the email column however, I get an error:
>
> SQL Error [42703]: ERROR: record "new" has no field "email"
>   Where: SQL statement "SELECT lower(new.email)"
> PL/pgSQL function on_record_modified() line 26 at assignment
>
> I have seen some hacks suggesting TRY/CATCH or converting to a JSON
> and checking if the field exists, but I would think that there's a
> better way to check if the field is in the NEW record, no?


I assume using to_jsonb(new) and then check for the key in the json value
will be faster than checking e.g. information_schema.column
or pg_catalog.pg_attribute


Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Pavel Stehule


po 5. 8. 2019 v 7:55 odesílatel Thomas Kellerer <[hidden email]> napsal:
Igal @ Lucee.org schrieb am 05.08.2019 um 00:52:
> I have the following statement in a trigger:
>
>     new.email = lower(new.email);
>
> When I try to update a record without setting the email column however, I get an error:
>
> SQL Error [42703]: ERROR: record "new" has no field "email"
>   Where: SQL statement "SELECT lower(new.email)"
> PL/pgSQL function on_record_modified() line 26 at assignment
>
> I have seen some hacks suggesting TRY/CATCH or converting to a JSON
> and checking if the field exists, but I would think that there's a
> better way to check if the field is in the NEW record, no?


I assume using to_jsonb(new) and then check for the key in the json value
will be faster than checking e.g. information_schema.column
or pg_catalog.pg_attribute

Alternative solution can be using other language than PLpgSQL - PLPythonu or PLPerl (there it is simple task). This language is not designed for too dynamic code. PLpgSQL triggers are designed for stable schema - you should to know if table has email column or not.

Catching errors in PLpgSQL is relative expensive solution due related savepoint overhead in background.

Regards

Pavel


Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Thomas Kellerer
Pavel Stehule schrieb am 05.08.2019 um 08:19:

>>> I have seen some hacks suggesting TRY/CATCH or converting to a JSON
>>> and checking if the field exists, but I would think that there's a
>>> better way to check if the field is in the NEW record, no?
>
>> I assume using to_jsonb(new) and then check for the key in the json value
>> will be faster than checking e.g. information_schema.column
>> or pg_catalog.pg_attribute
>
> Alternative solution can be using other language than PLpgSQL -
> PLPythonu or PLPerl (there it is simple task). This language is not
> designed for too dynamic code. PLpgSQL triggers are designed for
> stable schema - you should to know if table has email column or not.
>
> Catching errors in PLpgSQL is relative expensive solution due related
> savepoint overhead in background.
Yes, exception handling (or a catalog lookup) is expensive.
That's why I suggested that using to_jsonb() has the least overhead.

The check is then as simple as:

   if (to_jsonb(new) ? 'email') then
     ... do something
   end if;

Thomas


Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Pavel Stehule


po 5. 8. 2019 v 10:10 odesílatel Thomas Kellerer <[hidden email]> napsal:
Pavel Stehule schrieb am 05.08.2019 um 08:19:
>>> I have seen some hacks suggesting TRY/CATCH or converting to a JSON
>>> and checking if the field exists, but I would think that there's a
>>> better way to check if the field is in the NEW record, no?
>
>> I assume using to_jsonb(new) and then check for the key in the json value
>> will be faster than checking e.g. information_schema.column
>> or pg_catalog.pg_attribute
>
> Alternative solution can be using other language than PLpgSQL -
> PLPythonu or PLPerl (there it is simple task). This language is not
> designed for too dynamic code. PLpgSQL triggers are designed for
> stable schema - you should to know if table has email column or not.
>
> Catching errors in PLpgSQL is relative expensive solution due related
> savepoint overhead in background.
Yes, exception handling (or a catalog lookup) is expensive.
That's why I suggested that using to_jsonb() has the least overhead.

The check is then as simple as:

   if (to_jsonb(new) ? 'email') then
     ... do something
   end if;

casting from record to jsonb is not gratis too :).

But surely, it is cheaper than savepoints.

Pavel


Thomas


Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Michael Lewis
As a note to the original poster, you might want to check out-

Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Igal @ Lucee.org
On 8/5/2019 11:34 AM, Michael Lewis wrote:
> As a note to the original poster, you might want to check out-
>
> https://www.postgresql.org/docs/current/citext.html

Thanks, Michael.  I'm familiar with the citext module.

There is no reason, however, for an email address to be not-lower-cased,
so while in some cases (no pun intended) it makes sense to keep the
original CaSe while performing a case insensitive comparison, when it
comes to email addresses I rather collapse the value upon insertion/update.

Since that table has many more reads than writes, I am pretty sure that
it's more performant too.

Best,

Igal




Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

David G Johnston
In reply to this post by Igal @ Lucee.org
On Sun, Aug 4, 2019 at 3:52 PM Igal @ Lucee.org <[hidden email]> wrote:

I have the following statement in a trigger:

    new.email = lower(new.email);

When I try to update a record without setting the email column however, I get an error:

SQL Error [42703]: ERROR: record "new" has no field "email"
  Where: SQL statement "SELECT lower(new.email)"
PL/pgSQL function on_record_modified() line 26 at assignment

I have seen some hacks suggesting TRY/CATCH or converting to a JSON and checking if the field exists, but I would think that there's a better way to check if the field is in the NEW record, no?

Any ideas?

As already suggested you can spend not inconsiderable (you should measure it yourself) runtime time figuring out the schema of the table the trigger is attached to every single time it is invoked (even though the schema likely changes highly infrequently) or you can figure out a "compile time" way to program the schema structure into the individual function you are attaching to the trigger.

A hybrid approach would be to write the trigger function with an input argument (has_email_field boolean) and when attaching the function to the trigger attach it with either true/false depending on whether the target table has an email field.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: How to check if a field exists in NEW in trigger

Peter J. Holzer
In reply to this post by Igal @ Lucee.org
On 2019-08-07 15:54:33 -0700, Igal @ Lucee.org wrote:
> There is no reason, however, for an email address to be not-lower-cased, so
> while in some cases (no pun intended) it makes sense to keep the original
> CaSe while performing a case insensitive comparison, when it comes to email
> addresses I rather collapse the value upon insertion/update.

You should be aware that according to the specs, the local part of an
email address is case-sensitive. So [hidden email] and
[hidden email] might be two distinct email addresses.

Admittedly I've never seen that in practice (although I've seen a lot of
other problems caused by people who made unwarranted assumptions about
email addresses).

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | [hidden email]         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

signature.asc (849 bytes) Download Attachment