Re: Reg: PL/pgSQL commit and rollback

> From: Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)com>
>To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
>Sent: Tuesday, 17 March 2015, 14:30
>Subject: Re: [GENERAL] Reg: PL/pgSQL commit and rollback
>
>
>
>Yes. I have read this document.
>
>But my issue is that even when it throws
and exception I need to rollback the changes made by that query and move
on to the next block.
>
>Is there any way to accomplish that?
>

Yes, as per the docs in the link:

"When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back."

So you do something like:

BEGIN
UPDATE .....
EXCEPTION WHEN <whatever> THEN
.. <whatever>

END;