Re: Reg: PL/pgSQL commit and rollback

On 03/17/2015 07:30 AM, Medhavi Mahansaria wrote:
> 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?

"If no error occurs, this form of block simply executes all the
statements, and then control passes to the next statement after END. But
if an error occurs within the statements, further processing of the
statements is abandoned, and control passes to the EXCEPTION list. The
list is searched for the first condition matching the error that
occurred. If a match is found, the corresponding handler_statements are
executed, and then control passes to the next statement after END. If no
match is found, the error propagates out as though the EXCEPTION clause
were not there at all: the error can be caught by an enclosing block
with EXCEPTION, or if there is none it aborts processing of the function.

I have not looked at your original function in depth, but I am pretty
sure all you have to do is remove the COMMIT and ROLLBACK lines to get
what you want. There are also the RAISE_APPLICATION_ERROR lines to deal
with. Take a look at:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-errors-and-messages.html

Also might want to look at:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-porting.html
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com