Thread: 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
NEWand OLD that row triggers have, but I don' see anything @
http://www.postgresql.org/docs/current/static/plpgsql-trigger.htmlwhich 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/deletedrows? 

Thanks in advance,

Brian

OLD.column_name
NEW.column_name ?

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
massupdate at the end with a statement trigger will really be worth it for what I'm doing.   I might just end up doing
onlya row trigger. 

--brian