#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2010
    Posts
    5
    Rep Power
    0

    Rollback from function calling another function


    Hi,

    I have a function "function1" then I have another 2 functions "function2" & "function3". "function2" and "function3" insert records in different tables and a boolean value is returns true if the insert is successfull. "function1" calls the other 2 functions in sequence.

    "function1"
    DECLARE return_value BOOLEAN;
    BEGIN
    SELECT * FROM function2() INTO return_value;
    SELECT * FROM function3() INTO return_value;
    END;

    I need to rollback both functions if function3 is unsuccessfull. Can this be achieved cause function2 seems to commit the changes as soon as it finishes.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    A function in PostgreSQL can't commit, so function2 cannot commit "on its own".

    You are probably running in auto-commit mode.

    You need to call all functions inside a transaction then you can commit or rollback everything after the functions are finished.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2010
    Posts
    5
    Rep Power
    0
    Could you please give me an example.

IMN logo majestic logo threadwatch logo seochat tools logo