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.
