Re: pl/pgSQL & transaction

On Fri, 19 Jan 2001, Tibor Laszlo wrote:

> > This code doesn't work. I use Begin Work to start a transaction but BEGIN is
> > a PL/pgSQL command so I have a parse error when executing it.
> > 
> > CREATE FUNCTION a () RETURNS int4 AS '
> > BEGIN
> >    BEGIN WORK;
> >    COMMIT WORK;
> >     return 1;
> >     END;
> > ' LANGUAGE 'plpgsql';
> > 
> > What's wrong ????
> 
> Is it possible to use transactions in PL/pgSQL functions? AFAIK these functions
No, it is not possible.

> always(?) in a transaction (eg as triggers or as stored pprocs) and PostgreSQL
> doesn't support nested transactions.
Yes, each PL/pgSQL function call must be considered as a transaction (or,
if a transaction is BEGINned before the function call, then the function 
is run in the started transaction). Nested transactions are not supported
by PostgreSQL. If an error occurs inside the function, the transaction
will be aborted -- so none of the statements of the function (or, if a
transaction was BEGINned before the function call, then none of the other
statements before the function call inside the transaction) will be
committed to the database (they will be aborted). The only things happen
that the debug, notice and error messages come up.

Zoltan