Can I iterate over NEW in a PostgreSQL trigger function? -
i've written trigger function hoping iterate on new , check values.
create or replace function fix_nulls() returns trigger $_$ begin val in new loop if val = '{x:null}' val := ''; endif; endloop; return new; end $_$ language 'plpgsql'; create trigger prevent_nulls_siteinfo before update or insert on siteinfo each row execute procedure fix_nulls(); but syntax error:
error: syntax error @ or near "new" line 3: val in new ^ is possible iterate on values in new? write bunch of if statements check each column, i'd prefer function general can use other tables in future.
static code simple cases
for bunch of given columns spell out.
create or replace function fix_nulls() returns trigger $func$ begin if new.val1 = '{x:null}' new.val1 := ''; end if; if new.val2 = '{x:null}' new.val2 := ''; end if; if new.va31 = '{x:null}' new.val3 := ''; end if; return new; end $func$ language plpgsql; - don't quote language name
plpgsql, it's identifier.
and fire trigger when relevant:
create trigger prevent_nulls_siteinfo before update or insert on siteinfo each row when ('{x:null}' in (new.val1, new.val2, new.val3)) execute procedure fix_nulls();dynamic code
if there many columns or (better reason) columns (names) change lot, dynamic approach might warranted. however, while plpgsql for loop can iterate on rows in set (table) or on elements in array, can not same columns in row.
why?