At 12:23 PM 10/07/2004 +1000, Alan McDonald wrote:

> > I'm trying to work out if I can setup a transaction within a stored
> > procedure, or if this has to be done before the stored procedure is
> > executed?
> >
> > Myles
> >
>
>Starting/Stopping transactions are client activites. You also have
>savepoints at the server.

True, savepoints are used internally by the server during transaction
activity, but you don't have access to user savepoints (SAVEPOINT syntax)
in PSQL. The "nested transaction" mechanism in PSQL is achieved with
exceptions and exception handlers.

When your PSQL module (or the system) throws an exception, all work up to
the point where the exception occurs is rolled back to the beginning of the
block in which it it occurs. If this block includes a WHEN handler that
stops the exception from filtering outward, the last "good" work
effectively becomes a savepoint and execution will continue at the first
statement after the end of the block. If the exception occurs and is
handled inside an incomplete looping block, execution will resume at the
next iteration of the loop.

Exceptions are real fun to play with. Try it. :-)

/heLen