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