postgreSQL 9.4 how to rollback from trigger

Question

I have the function insert_val that inserts some values into some tables. These tables have triggers that raises exception when something is wrong. How can I rollback the function whenever a trigger raises exception and go to the next curs1 is this possible? Thanks in advance

res :='start';
OPEN curs1 FOR SELECT temp3.fid FROM temp3; 
LOOP 

FETCH curs1 INTO fidVar;
EXIT WHEN NOT FOUND;
BEGIN
if raise_exception then
rollback;
end if;
perform insert_val(fidVar,startDate,endDate);    
END;
END LOOP;

Craig Ringer · Accepted Answer · 2015-05-29 01:41:04Z

1

You cannot ROLLBACK from a trigger or function, because transaction control is not possible within functions.

What you actually seem to want is to skip and continue after an exception. You can do this using BEGIN ... EXCEPTION blocks in PL/PgSQL, per the manual. Internally this uses subtransactions just like SAVEPOINT and ROLLBACK TO SAVEPOINT.

answered May 29, 2015 at 1:41

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

postgreSQL 9.4 how to rollback from trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related