Postgres Pro
Downloads
Home   >   mailing lists

Re: Reg: PL/pgSQL commit and rollback - Mailing list pgsql-general

From Medhavi Mahansaria
Subject Re: Reg: PL/pgSQL commit and rollback
Date
Msg-id OFA056DBA3.8B770938-ON65257E0B.004F7A8B-65257E0B.004FAB7D@tcs.com
Whole thread Raw
In response to Re: Reg: PL/pgSQL commit and rollback  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Reg: PL/pgSQL commit and rollback
Re: Reg: PL/pgSQL commit and rollback
Re: Reg: PL/pgSQL commit and rollback
List pgsql-general
Tree view
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.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                       Business Solutions
                       Consulting
____________________________________________



From:        Adrian Klaver <adrian.klaver@aklaver.com>
To:        Medhavi Mahansaria <medhavi.mahansaria@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.klaver@aklaver.com

pgsql-general by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: Reg: PL/pgSQL commit and rollback
Next
From: Alban Hertroys
Date:
Subject: Re: Reg: PL/pgSQL commit and rollback