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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
