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
