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.klaver@aklaver.com