Re: 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?

Thanks & Regards
Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi(dot)mahansaria(at)tcs(dot)com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)com>,
"pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Date: 03/17/2015 07:29 PM
Subject: Re: [GENERAL] Reg: PL/pgSQL commit and rollback

On 03/17/2015 06:50 AM, Medhavi Mahansaria wrote:
> Hi,
>
> 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.
>
> In oracle:
>
> *CREATE OR REPLACE PROCEDURE abc (STATUS IN NUMBER) AS*
>
> * CODE NUMBER;*
> * MSG NVARCHAR2(200);*
>
> *BEGIN*
> * DELETE FROM LOG;*
> * DELETE FROM TRACKER;*
> * BEGIN*
> * IF (STATUS < 1)*
> * THEN*
> * <some query>*
>
> * INSERT INTO TRACKER SELECT 1,SYSDATE FROM DUAL;*
> * COMMIT;*

The above is your problem, there cannot be a COMMIT in the function.

See here for more detail;

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

> * END IF;*
> * EXCEPTION*
> * WHEN OTHERS THEN*
> * CODE:=SQLCODE;*
> * MSG:= SQLERRM;*
> * ROLLBACK;*
> * INSERT INTO LOG('CODE AND MESSAGES ARE ' || CODE || ' '
> || MSG);*
> * COMMIT;*
> * RAISE_APPLICATION_ERROR(-20001, 'EXCEPTION WHEN EXT SYSTEM ID
> KEY');*
> * END;*
> * BEGIN*
> * IF (STATUS < 5)*
> * THEN*
> * <some query>*
> **
> * INSERT INTO TRACKER SELECT 5,SYSDATE FROM DUAL;*
> * COMMIT;*
> * END IF;*
> * EXCEPTION*
> * WHEN OTHERS THEN*
> * CODE:=SQLCODE;*
> * MSG:= SQLERRM;*
> * ROLLBACK;*
> * INSERT INTO LOG('CODE AND MESSAGES ARE ' || CODE || ' '
> || MSG);*
> * COMMIT;*
> * RAISE_APPLICATION_ERROR(-20001, 'EXCEPTION WHEN EXT SYSTEM ID
> KEY');*
> * END;*
> *
> ............... and so on (I have blocks toll STATUS < 200 and all
> follow the same concept)*
> *END;*
> */*
>
>
> How can i acheive the same output/flow in PL/pgSQL?
>
> Can you please share a converted code snippet for my reference.
>
>
> Thanks & Regards
> Medhavi Mahansaria
> Cell:- +91 9620053040
>
> =====-----=====-----=====
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com