CODEWITHSUNDEEP
postgresqldatabase-migrationpostgresql-9.5
Sanjum
Sanjum

Reputation: 15

commit and rollback inside the postgres function

How can i implement commit and rollback in my postgres function , code is as below. rollback here is used in exceptional block

    CREATE OR REPLACE FUNCTION test () RETURNS VOID AS $body$
DECLARE

test_var smallint;

BEGIN
FOR abc IN
(SELECT DISTINCT ID
    FROM plantab abc
    JOIN xxx_upl_att xxx_val ON zzz_val.idf_tec = abc.ID
)
LOOP
    select * from abc_pk_attribut(abc.ID) into test_var;
    select * from abc_pk_champ(abc.ID) into test_var;
    select * from abc_pk_valeur(abc.ID) into test_var;
    select * from abc_pk_m_att(abc.ID) into test_var;
    PERFORM abc_pk_maj_avalorise(abc.ID);
    PERFORM abc_pk_maj_ab_attr(abc.ID);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise notice 'program_error';
END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

Upvotes: 0

Views: 6878

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

You cannot to do it. PostgreSQL functions is running inside outer transaction, and this outer transaction should be committed or rollback outside. When you run function from SELECT statement, then this statement is executed under implicit transaction (in autocommit mode) or explicit transaction (when autocommit is off). Who started this transaction, then he has to finish transaction.

What you can:

  1. you can raise a exception - when this exception is not handled, then outer statement has to run rollback.

  2. you can use clause EXCEPTION WHEN. Then protected block is implicitly joined with savepoint. When any exception is handled, then engine use ROLLBACK TO savepoint implicitly.

Anyway, this system is different than you can know from MS-SQL or Oracle, and you cannot to use some patterns that you know from Oracle. On second hand - Postgres design is much simple.

New PostgreSQL 11 has procedures, and there you can use explicit COMMIT and ROLLBACK.

Upvotes: 2

Related Questions