Can I iterate over NEW in a PostgreSQL trigger function?

Question

I've written a trigger function hoping to iterate over NEW and check all its values.

CREATE OR REPLACE FUNCTION fix_nulls() RETURNS TRIGGER AS $_$
BEGIN
    FOR 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
    FOR EACH ROW
    EXECUTE PROCEDURE fix_nulls();

but I get a syntax error:

ERROR:  syntax error at or near "NEW"
LINE 3:  FOR val IN NEW
                    ^

Is it possible to iterate over all values in NEW? I could easily write a bunch of if statements to check each column, but I'd prefer this function be general so I can use it for other tables in the future.

http://stackoverflow.com/questions/25148585/pl-pgsql-general-way-to-update-n-columns-in-trigger — , Apr 09 '15 at 20:33

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Static code for simple cases

For just a bunch of given columns I would just spell it out.

CREATE OR REPLACE FUNCTION fix_nulls()
  RETURNS TRIGGER AS
$func$
BEGIN
   IF NEW.val1 = '{x:Null}' THEN NEW.val1 := ''; END IF;
   IF NEW.val2 = '{x:Null}' THEN NEW.val2 := ''; END IF;
   IF NEW.va31 = '{x:Null}' THEN NEW.val3 := ''; END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Don't quote the language name plpgsql, it's an identifier.

And only fire the trigger when relevant:

CREATE TRIGGER prevent_nulls_siteinfo
BEFORE UPDATE OR INSERT ON siteinfo
FOR EACH ROW 
WHEN ('{x:Null}' IN (NEW.val1, NEW.val2, NEW.val3))
EXECUTE PROCEDURE fix_nulls();

Dynamic code

If there are many columns or (better reason) columns (names) change a lot, a dynamic approach might be warranted. However, while a plpgsql FOR loop can iterate over rows in a set (table) or over elements in an array, it can not do the same with columns in a row.
Why?

PL/pgSQL: General Way to Update N Columns in Trigger?

This worked great! I had to make one minor change to the trigger: the `FOR` had to be before the `WHEN` with the `EXECUTE PROCEDURE` bringing up the rear. — raddevon, Apr 09 '15 at 23:57
@raddevon: Right, thanks, fixed. That got mixed up with my fancy formatting. — Erwin Brandstetter, Apr 10 '15 at 00:06

Can I iterate over NEW in a PostgreSQL trigger function?

1 Answers1

Static code for simple cases

Dynamic code