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.

stackoverflow.com/questions/25148585/…

user330315
– user330315

2015-04-09 20:33:07 +00:00
Commented Apr 9, 2015 at 20:33 — user330315
– user330315, Commented Apr 9, 2015 at 20:33

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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?

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 9, 2015 at 22:42

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

raddevon Over a year ago

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.

Erwin Brandstetter Over a year ago

@raddevon: Right, thanks, fixed. That got mixed up with my fancy formatting.

Collectives™ on Stack Overflow

Can I iterate over NEW in a PostgreSQL trigger function?

1 Answer 1

Static code for simple cases

Dynamic code

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Static code for simple cases

Dynamic code

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related