rollback previous commit if the current one fails

Started by Alicja Kucharczykalmost 5 years ago3 messagesgeneral
Jump to latest
#1Alicja Kucharczyk
zaledwie10minut@gmail.com

Hi,
i need to emulate oracle's savepoint behaviour inside of the plpgsql
function.

This function is able to insert all the rows that weren't caught on the
exception, but i need also to rollback the insert that happens before the
exception.

So let's say the exception is thrown when j=3 so i need also to rollback
j=2.
Any idea how to approach it?

DROP TABLE IF EXISTS test;
CREATE TABLE test
(
id INT
);

CREATE OR REPLACE PROCEDURE test()
AS
$$
DECLARE
j INT;
BEGIN

FOR j IN 0..6
LOOP
BEGIN
INSERT INTO test VALUES (1 / j);
EXCEPTION
WHEN OTHERS THEN
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;

CALL test();
TABLE test;

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

#2Noname
luis.roberto@siscobra.com.br
In reply to: Alicja Kucharczyk (#1)
Re: rollback previous commit if the current one fails

----- Mensagem original -----

Any idea how to approach it?

Hi!

https://www.postgresql.org/docs/current/sql-savepoint.html

Luis R. Weck

#3Alicja Kucharczyk
zaledwie10minut@gmail.com
In reply to: Noname (#2)
Re: rollback previous commit if the current one fails

thank you Luis, but this is not supported in plpgsql

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html