postgreSQL for-each-row before-update triggers : the NEW and OLD pseudo-rows

Question

Does the function that returns the trigger automagically have the ability to reference the NEW and OLD pseudo-rows in a before-update trigger designated for each row?

 CREATE TRIGGER foo_trigger BEFORE UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE foo();

   CREATE FUNCTION foo() RETURNS trigger AS $foo_trigger$
    BEGIN  
        NEW.taxrate := 5.5;        
        RETURN NEW;
    END;
    $foo_trigger$ LANGUAGE plpgsql;

And must the string inside $....$ in the final line of the function $foo_trigger$ LANGUAGE plpgsql; exactly match the name of the trigger in the CREATE TRIGGER statement, or is it just a placeholder?

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2013-01-23T23:28:34

automagically have the ability to reference the NEW and OLD pseudo-rows in a before-update trigger designated for each row

Yes.

Quote from the manual http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block.

Must the string [...] exactly match the name of the trigger in the CREATE TRIGGER statement, or is it just a placeholder

No.

The $..$ thing is called "dollar quoting" and simply replaces single quotes to make handling large strings (with embedded quotes) easier. The only requirement is that you use the same "key" at the start and at the end. You can use $body$ (which is very common) or anything you like.

See the manual for details: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

postgreSQL for-each-row before-update triggers : the NEW and OLD pseudo-rows

1 Answers