Thread: Problem with stored procedure and nested transactions
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
