Thread: rollback previous commit if the current one fails

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

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

> Any idea how to approach it?


Hi!

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



Luis R. Weck 

thank you Luis, but this is not supported in plpgsql



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