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

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