Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015
    Posts
    2

    Unanswered: How to do commit inside a function in postgresql

    Hi,
    We are facing issues when we commit inside a transaction in postgresql stored procedure

    Below is the oracle stored procedure for the same,

    Can you please share postgresql stored procedure which will function same as below oracle stored procedure.

    -- Procedure to delete entries for Worklists based on Organization context using a given batch size.
    CREATE OR REPLACE PROCEDURE DELETE_NOTF_WORKLIST(
    ORGDN VARCHAR2,
    BATCH_SIZE NUMBER)
    AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    QUERY_WORKLIST VARCHAR2(1000);
    LOCAL_ROW_COUNT NUMBER;
    TEMP_WORKLIST_ID VARCHAR2(50);
    TYPE WL_CURSOR_TYPE IS REF CURSOR;
    WL_CURSOR WL_CURSOR_TYPE;
    BEGIN
    QUERY_WORKLIST := 'SELECT ID FROM NOTF_WORKLIST WHERE ORGANIZATION=:ORGDN AND ROWNUM <=:BATCH_SIZE';
    LOCAL_ROW_COUNT := BATCH_SIZE;

    LOOP
    EXIT WHEN LOCAL_ROW_COUNT < BATCH_SIZE;
    OPEN WL_CURSOR FOR QUERY_WORKLIST USING ORGDN , BATCH_SIZE;

    LOOP

    FETCH WL_CURSOR INTO TEMP_WORKLIST_ID;
    EXIT WHEN WL_CURSOR%NOTFOUND;

    DELETE FROM NOTF_WORKLIST_CUSTOMIZATION
    WHERE NOTF_WORKLIST_CUSTOMIZATION.CONTAINER_ID = TEMP_WORKLIST_ID;

    DELETE FROM NOTF_TEAM_WORKLIST_MAPPING
    WHERE NOTF_TEAM_WORKLIST_MAPPING.WORKLIST_ID = TEMP_WORKLIST_ID;

    DELETE FROM NOTF_WORKLIST
    WHERE NOTF_WORKLIST.ID = TEMP_WORKLIST_ID;

    COMMIT;

    END LOOP;

    LOCAL_ROW_COUNT := WL_CURSOR%ROWCOUNT;
    CLOSE WL_CURSOR;

    END LOOP;

    EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20000, 'Error occured while deleting worklist data. Details : ' || SQLERRM);

    END DELETE_NOTF_WORKLIST;

    Thanks,
    Raghuram

  2. #2
    Join Date
    Nov 2015
    Posts
    2
    how do we get the row number?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by raghuiiitb View Post
    Hi,
    We are facing issues when we commit inside a transaction in postgresql stored procedure

    Below is the oracle stored procedure for the same,

    Can you please share postgresql stored procedure which will function same as below oracle stored procedure.
    You can't.
    Postgres only has functions, and functions are always part of the calling transaction.
    You will have to move the commit to the caller of the function.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •