Re: Rollback with functions

A B <gentosaker(at)gmail(dot)com> writes:
> Assume I have a function

> create function foo()
> begin
> do stuff
> ....
> .... <--- here it fails
> ....
> end

> and a call to the function fails (as indicated in the code), will
> everything that has been done inside the function be automatically
> undone (rollbacked)?

Yes, it rolls back to the start of the transaction, which in this case
is outside the function altogether.

> If I had added code for exceptions, like this

> create function foo()
> begin
> begin
> do stuff
> ....
> ....
> ....
> exception when others then
> .... <--- clean up code
> end;
> end

> Then I would need the "clean up code", or else there would be some
> changes in the database caused by all the commands that were actually
> run before the failure, right?

When control gets to the "clean up code", everything that happened
inside the begin-block is already rolled back. (An exception is that
the local variables of the function still have the values they had
when the error was thrown. But effects out in the database have been
undone.)

regards, tom lane