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

po 5. 8. 2019 v 10:10 odesílatel Thomas Kellerer <spam_eater(at)gmx(dot)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
>
>
>