From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to check if a field exists in NEW in trigger |
Date: | 2019-08-05 06:19:56 |
Message-ID: | CAFj8pRA7XM3SL2Dp9=_SJk6v7Us+4N42HXK7OSEUFrKv-ibV5A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
po 5. 8. 2019 v 7:55 odesílatel Thomas Kellerer <spam_eater(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Shital A | 2019-08-05 06:30:14 | Compression In Postgresql 9.6 |
Previous Message | Thomas Kellerer | 2019-08-05 05:55:29 | Re: How to check if a field exists in NEW in trigger |