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.

Answer 1

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?