Reg: PL/pgSQL commit and rollback

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

Responses

Browse pgsql-general by date

  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?