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

On 8/4/19 3:52 PM, 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,

Do you mean:

1) There is no actual email column?

2) There is an email column but no value for it?

> 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

Is:

on_record_modified()

a generic function that will be applied to many tables with differing
schema?

>
> 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?

Use

TG_RELID

and look up the columns in:

https://www.postgresql.org/docs/11/catalog-pg-attribute.html

Or use the columns information_schema:

https://www.postgresql.org/docs/11/infoschema-columns.html

>
> Any ideas?  Thanks!
>
> Igal Sapir
> Lucee Core Developer
> Lucee.org <http://lucee.org/>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com