Well, if it is a limitation, and having it would lead to a "better product", why not making it a feature for the next still-open release? In my opinion that's more than a limitation, it's a missing feature. In your code you often need to create savepoints to delay the decision for the commitment. A Pl/PgSQL function is just a bunch of code you want to move into the DB. So the need for savepoints seems to me to be still there. Useless to say I would vote for a "GO". On Thursday 02 October 2008 11:01:37 Albe Laurenz wrote: > Alvaro Herrera wrote: > > > > Is there a way to have (sub)transactions within a function body? > > > > I'd like to execute some code (a transaction!) inside a function and > > > > later decide whether that transaction is to be committed or not. > > > > > > You could issue a "SAVEPOINT name". If at the end you don't want your > > > changes to apply, you can issue a "ROLLBACK to name" > > > > Actually you can't use SAVEPOINT nor ROLLBACK TO within a function. In > > PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes, > > just do a RAISE EXCEPTION, and the exception block is run). > > After a discussion on comp.databases.postgresql I realized that this > is actually a limitation. > > Consider the following: > > BEGIN > UPDATE ... > UPDATE ... > UPDATE ... > EXCEPTION > WHEN integrity_constraint_violation THEN > ... > END; > > If the first UPDATE succeeds but the second one bombs, there is no way > to undo the first update short of having the whole transaction cancelled. > > So while exceptions are implemented using savepoints, they give you only > part of the functionality, namely to make a group of statements > all-or-nothing within one transaction. > > If you need all three of these UPDATEs to either all succeed or fail, > but the whole transaction should continue, you cannot do that in PL/pgSQL. > > Is there a chance to get savepoint support in PL/pgSQL at some point? > Does it make sense to raise this on -hackers? > > Yours, > Laurenz Albe
