I have a psql function that selects for update and then iterates through an array to update the table. If an error is found it correctly returns false, but does not rollback. So if the error is half way through the array, the first half will have been updated.

I was under the impression that all postgres functions were transactional. Is this not the case?

Should I look into converting my function to a stored procedure so I can get the benefits of transactions?

Thank you

  • The function participates in the transaction of the caller, so yes they are transactional. But you can't have a commit or rollback inside a function to begin with. The caller needs to do the rollback or the function needs to raise an error - then caller is forced to do a rollback
    – user1822
    Oct 6, 2021 at 9:52
  • Thank you very much a_horse_with_no_name :)
    – user973347
    Oct 6, 2021 at 9:55


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.