From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | 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 08:09:59 |
Message-ID: | ea13f32c-492c-4215-1308-aa12039104b6@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2019-08-05 08:34:27 | Re: How to check if a field exists in NEW in trigger |
Previous Message | Ron | 2019-08-05 07:11:53 | Re: Compression In Postgresql 9.6 |