From: | Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Reg: PL/pgSQL commit and rollback |
Date: | 2015-03-17 13:50:16 |
Message-ID: | OFC7E7638A.886D2FD1-ON65257E0B.00476A01-65257E0B.004C0397@tcs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ruth Melendo | 2015-03-17 13:58:40 | Re: pgpool + BDR, is it possible? |
Previous Message | jaime soler | 2015-03-17 13:38:28 | Re: pgpool + BDR, is it possible? |