Re: Reg: PL/pgSQL commit and rollback

Medhavi Mahansaria wrote:
> I am writing a porting a procedure running in oracle to a PL/pgSQL function.
>
> I need to use commit and rollback in my function.
>
> I have read that usage of commit and rollback is not possible in PL/pgSQL, however savepoints can be
> used.
>
> even when i use savepoints and rollback to a savepoint in the exception block I am getting the
> following error
>
> ERROR: cannot begin/end transactions in PL/pgSQL
> HINT: Use a BEGIN block with an EXCEPTION clause instead.

Instead of explicitly using ROLLBACK, you have to code like this:

BEGIN
INSERT ... -- may cause an error
EXCEPTION
WHEN OTHERS
THEN ...
END

If you get into the exception block, PL/pgSQL will implicitly
have rolled back everything that happened between BEGIN and EXCEPTION.

Yours,
Laurenz Albe