Old values in statement triggers?
Is it possible to reference the old values in a statement trigger using plpgsql? I'm looking for something similar to NEW and OLD that row triggers have, but I don' see anything @ http://www.postgresql.org/docs/current/static/plpgsql-trigger.html which doesn't really offer any examples for statement triggers.
If what I'm looking for doesn't exist, are there any common workarounds that people use to find the set of updated/inserted/deleted rows?
Thanks in advance,
Brian
2010/10/21 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
OLD.column_name
NEW.column_name ?
I believe OP is asking specifically about statement-level triggers. As
the docs <http://www.postgresql.org/docs/current/static/trigger-definition.html>
say:
| Statement-level triggers do not currently have any way to
| examine the individual row(s) modified by the statement.
What I've done is to have a row-level trigger that populates a
temporary table with "interesting" changes that need further
processing, and then a statement-level trigger which does bulk-updates
based on what's in that temporary table. This comes in quite handy
when bulk-loading data, e.g. with COPY.
Josh
Thanks Josh,
On Oct 21, 2010, at 7:49 AM, Josh Kupershmidt wrote:
2010/10/21 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
OLD.column_name
NEW.column_name ?I believe OP is asking specifically about statement-level triggers. As
Yup.
the docs <http://www.postgresql.org/docs/current/static/trigger-definition.html>
say:
| Statement-level triggers do not currently have any way to
| examine the individual row(s) modified by the statement.
I don't know how I didn't see that in the docs when I was looking. It must have been sleepy morning eyes or lack of coffee.
What I've done is to have a row-level trigger that populates a
temporary table with "interesting" changes that need further
processing, and then a statement-level trigger which does bulk-updates
based on what's in that temporary table. This comes in quite handy
when bulk-loading data, e.g. with COPY.
I'll look at doing something like you describe, although I wonder if the overhead of doing a row trigger and then a mass update at the end with a statement trigger will really be worth it for what I'm doing. I might just end up doing only a row trigger.
--brian