Re: Reg: PL/pgSQL commit and rollback

> From: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>
>To: Adrian Klaver <adrian.klaver@aklaver.com>
>Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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
theerror 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;