Postgres error relation "new" does not exist on insert trigger

Question

Below trigger executes with out any error, but throws

ERROR:  relation "new" does not exist

on inserting single row into tbl1

CREATE OR REPLACE FUNCTION update_tbl1() 
    RETURNS TRIGGER AS
    $BODY$ DECLARE
DECLARE no_of_rows INT;
BEGIN
    SELECT COUNT(*) as no_of_rows FROM NEW; --error location
         ...
         ...
    COMMIT TRANSACTION
;
END; 
 $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER update_tbl2 AFTER INSERT ON tbl1
     EXECUTE PROCEDURE update_tbl1();

I am unable refer INSERTED, DELETED (SQL SERVER) rows in postgres using NEW and OLD, I appreciate any help.

I need to update a table similar to this

Table-Bill
item  cost  subtotal
itm1  1     1
itm2  5     6
itm3  4     10
itm4  3     13

My input is 'item' and 'cost' column, and trigger needs to update 'subtotal' with sum of all items inserted so far, I am not sure whether to use 'FOR EACH ROW' or for statement and then use cursor please help with solution with good performance

Unlike SQL Server, Postgres supports row triggers, there is no "inserted" table in a row trigger. What exactly are you trying to do? And another thing: you can not commit or rollback in a trigger function (or any function that is). — user330315
– user330315, Commented Mar 20, 2014 at 8:11
@safetyOtter "SQL" does not mean "Microsoft SQL Server". It is the query language. Don't assume that sql means sql-server. — Craig Ringer
– Craig Ringer, Commented Mar 20, 2014 at 8:12
@safetyOtter: that is not a valid identifier in Postgres (or any ANSI standard compliant DBMS) — user330315
– user330315, Commented Mar 20, 2014 at 8:12
What do you want to do with the result of the select count(*) ? maybe "assign" it somehow ? — joop
– joop, Commented Mar 20, 2014 at 11:56

Community · Accepted Answer · 2017-05-23 12:05:21Z

3

FOR EACH STATEMENT does not support NEW and OLD usage so only way in current postgres is to go by FOR EACH ROW to access updated/inserted/deleted rows check this post for more information How to use 'for statement' triggers in postgres?

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Mar 24, 2014 at 5:53

RAFIQ

1,0853 gold badges19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Postgres error relation "new" does not exist on insert trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related