How to use 'for statement' triggers in postgres?

Question

if we write a trigger FOR STATEMENT like below how can we access only updated rows in trigger procedure/function

CREATE FUNCTION func1()
RETURNS trigger AS $$
BEGIN
    --access only updated/inserted rows here???
    RETURN null;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig1
AFTER UPDATE OR DELETE OR INSERT
ON tbl1 FOR STATEMENT
EXECUTE PROCEDURE func1();

I mean when there are multiple rows updated once like below

update tbl1 set col1=1 where col2 in (2,3,4,5,6)

I don't think you can. I think you need to use row triggers and go through NEW and OLD. — mu is too short
– mu is too short, Commented Mar 24, 2014 at 5:10
@mu is too short thanks for reply, I am using postgres 9.3, you mean it is not supported yet? there is no performance difference even if we use FOR EACH ROW for table updates of hundreds or thousands rows at once? — RAFIQ
– RAFIQ, Commented Mar 24, 2014 at 5:20
@RAFIQ There'd be a big performance difference if it was supported, but since PostgreSQL doesn't support NEW and OLD for FOR EACH STATEMENT triggers, FOR EACH ROW is your only option. — Craig Ringer
– Craig Ringer, Commented Mar 24, 2014 at 5:21

jojo2000 · Accepted Answer · 2018-05-26 17:43:01Z

8

Now, it is possible to use an identifier for the table of inserted records in pg 10 :

CREATE TRIGGER transfer_insert
AFTER INSERT ON transfer
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE PROCEDURE check_transfer_balances_to_zero();

Extracted from pg 10 doc : https://www.postgresql.org/docs/10/static/sql-createtrigger.html

answered May 26, 2018 at 17:43

jojo2000

1131 silver badge3 bronze badges

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

Comments

Craig Ringer · Accepted Answer · 2014-03-24 05:20:23Z

2

At this time there is no support for NEW and OLD pseudo-relations for FOR EACH STATEMENT triggers.

You must use FOR EACH ROW triggers.

In theory it's possible for PostgreSQL to have fake tables for the new- and old- row versions, connected together by some one-off generated key. Or a single table containing the new and old tuples as composite types. However, this is not currently supported, and as far as I know nobody is working on support for it.

For some applications it can be worth using FOR EACH ROW triggers to INSERT into a TEMPORARY table, then process the whole lot in a final FOR EACH STATEMENT trigger.

answered Mar 24, 2014 at 5:20

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

1 Comment

RAFIQ Over a year ago

Thanks very much for clarification, i really wasted lot of time looking for help, now will move on with FOR EACH ROW triggers

Collectives™ on Stack Overflow

How to use 'for statement' triggers in postgres?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related