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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: How to check if a field exists in NEW in trigger
Дата
Msg-id CAKFQuwZuhD=eMn-_KqqLCs3raSW+Dh=8HkeCgUNsQp-oFiKiLA@mail.gmail.com
обсуждение исходный текст
Ответ на How to check if a field exists in NEW in trigger  ("Igal @ Lucee.org" <igal@lucee.org>)
Список pgsql-general
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 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?

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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Bryn Llewellyn
Дата:
Сообщение: Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
Следующее
От: Bryn Llewellyn
Дата:
Сообщение: Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?