1

Clearly I am misunderstanding rollbacks in PostgreSQL, why doesn't work this piece of script?:

do $$
begin
        insert into mytable values (1);
        insert into mytable values (1);
exception when others then 
        ROLLBACK;    
end;
$$ language 'plpgsql';

ERROR: cannot begin/end transactions in PL/pgSQL HINT: Use a BEGIN block with an EXCEPTION clause instead. CONTEXT: PL/pgSQL function inline_code_block line 13 at SQL statement *** Error ***

Jero Lopez
  • 398
  • 1
  • 7
  • 18
  • 3
    Because functions (and a `DO` block is also one) are not allowed to explicitly commit or rollback a transaction: –  Feb 14 '14 at 12:45
  • Sorry I don't understand, do you mean there is no way to rollback in a function? The only thing I want to achieve is creating a script which execute some INSERTs inside a transaction and if something goes wrong then rollback all those INSERTs, how can I do that? – Jero Lopez Feb 14 '14 at 12:48
  • You need to do the transaction handling *outside* the function. –  Feb 14 '14 at 12:50
  • 11
    @JeroLopez You do not need to explicitly call `ROLLBACK`. An uncaught exception inside a function will cause rollback automatically. – Ihor Romanchenko Feb 14 '14 at 12:55
  • Thanks Igor, that was what I was missing. – Jero Lopez Feb 14 '14 at 12:57

0 Answers0