rollback previous commit if the current one fails

From: pinker <zaledwie10minut(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: rollback previous commit if the current one fails
Date: 2021-04-13 08:29:22
Message-ID: 1618302562353-0.post@n3.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ma Xinjian 2021-04-13 09:38:10 "missing chunk number 0 for toast value xxx in pg_toast_xxx" when pg_basebackup
Previous Message LE MENTEC, SANDRINE 2021-04-13 08:10:06 looking for a installation package to Using GSSAPI with Postgres12 for windows