Problem with stored procedure and nested transactions - Mailing list pgsql-general

Hi everyone,

I have problems with stored procedures introduced in version 11.

I do not understand how to create a nested transaction, in this semplified example:

create or replace procedure tst_prc(inout p_cod text) language plpgsql as $procedure$

begin

p_cod := 'a';

       begin

             update aziende set mail = 'asd@asd.asd' where id = 11; --1st update

             begin

                    update aziende set telefono = '0123456789' where id = 11; --2nd update

                    commit;

                    raise notice 'Inner';

             end;

             update aziende set telefono = '089' where id = 11; --3rd update

             --commit;

             rollback;

             raise notice 'Outer';

       end;

p_cod := 'b';

end;

$procedure$

The third update goes, rightly, in rollback; the problem is that the first 2 are committed.

I thought that the “BEGIN/END” block was used to create new transactions and that each of them could be managed individually.

What am I doing wrong?

Best regards.

Paolo Pierotti

Viale Lombardia, 4 Lodi (LO)
M: +39 328 9035851

P: +39 075 8556435

W: www.mmbb.it