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

On Sun, Aug 4, 2019 at 3:52 PM Igal @ Lucee.org <igal(at)lucee(dot)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.