Обсуждение: How to check if a field exists in NEW in trigger
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
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 adrian.klaver@aklaver.com
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
po 5. 8. 2019 v 7:55 odesílatel Thomas Kellerer <spam_eater@gmx.net> 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
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
po 5. 8. 2019 v 10:10 odesílatel Thomas Kellerer <spam_eater@gmx.net> 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
As a note to the original poster, you might want to check out-
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
On Sun, Aug 4, 2019 at 3:52 PM Igal @ Lucee.org <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, 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 assignmentI 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.
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 JoeSmith@example.com and
JoESmith@example.com 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
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>