CODEWITHSUNDEEP
postgresqlstored-procedurestriggers
Pavel V.
Pavel V.

Reputation: 2790

How to pass NEW/OLD to procedures called from trigger functions in PostgreSQL?

Let's suppose I have a trigger function, but its code is so complex that it would be handy to split it to more procedures. I want to operate with the NEW (or OLD for UPDATE/DELETE triggers) variable in all of them. Is there any alternative to sending it as a function parameter to every procedure called from the original trigger function?

Upvotes: 3

Views: 4515

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

If you really need to pass them around, I'm quite sure you can do so as appropriately typed records (e.g. newrow tablename%ROWTYPE).

http://www.postgresql.org/docs/current/static/plpgsql-declarations.html

That said, unless you run giant queries whose results you use throughout the trigger function, breaking the trigger into smaller parts is usually a cleaner than calling subfunctions imho. Note that you can conditionally execute the triggers, e.g.:

create trigger "01_do_stuff_upd" on update after tablename
  for each row
  when (old.field <> new.field and ...)
execute procedure do_stuff_upd_part_01();

The thing to have in mind when doing the above, is that in Postgres (and contrary to the sql spec), the triggers are executed in alphabetical order rather than in the order they're created.

Upvotes: 5

Related Questions