Search Postgresql Archives

Reg: PL/pgSQL commit and rollback

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;
                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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux