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.mahansa...@tcs.com Website: http://www.tcs.com ____________________________________________ Experience certainty. IT Services Business Solutions Consulting ____________________________________________ From: Adrian Klaver <adrian.kla...@aklaver.com> To: Medhavi Mahansaria <medhavi.mahansa...@tcs.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.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.kla...@aklaver.com
