Thread: Savepoint/Rollback in functions
I am running postgres 8.1.2 on both a windows and Linux platform. The primary method of managing business logic is through the use of functions. Our primary access to the database is by using PSQL (pg/psql). The problem that I am having is that we need to execute and implement "SAVEPOINT" and "ROLLBACK" functionality inside of FUNCTIONS. The "SAAVEPOINT/ROLLBACK" works fine interactively. At the present time the documentation does not seem to be definitive on the functionality of this, BUT, we placed "SAVEPOINT/ROLLBACK" commands in a test function and the function failed. Question 1: does pg/psql functions allow "SAVEPOINT/ROLLBACK" functionality? (if so how?) Question 2: if the answer to question 1 is "NO", what would be required to get this implemented in the standard product? Thanks in advance for any information and help. Scott.
Scott Petersen wrote: Hi, > Question 1: does pg/psql functions allow "SAVEPOINT/ROLLBACK" > functionality? (if so how?) Yes. However, you cannot use that syntax directly. You rather use it by establishing EXCEPTION clauses in BEGIN/END blocks. Upon entering any BEGIN/END block which has an EXCEPTION clause, an implicit SAVEPOINT is executed. If any exception (read: error) is found while executing the block, the savepoint will be automatically rolled back and control passed to the EXCEPTION block. HTH, -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.