Tim Smith wrote on Friday, June 26, 2015 5:38 PM:
> ERROR: cannot begin/end transactions in PL/pgSQL
> HINT: Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT: PL/pgSQL function
> app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
> line 16 at SQL statement
> Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"
I believe I've read you can have nested BEGIN ... END blocks, and the transaction control is done implicitly by the
PL/pgSQLexception handling, so you probably can write
BEGIN
...
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
...
END
END;
which would (hopefully) only roll back the second ... not the first ... (not sure if you still need to declare the
savepoint,at least, as you found out, explicitly rolling back to the savepoint is not allowed in PL/pgSQL). Note that
thethird ... probably should not raise or re-raise an exception, otherwise you have an exception in the outer BEGIN-END
blockand everything is rolled back.
Best regards
Holger Friedrich