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

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