Postgres Pro
Downloads
Home   >   mailing lists

Thread: pl/pgSQL & transaction

pl/pgSQL & transaction

From
"Zolof"
Date:
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 ????

Re: pl/pgSQL & transaction

From
Tibor Laszlo
Date:
> 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
always(?) in a transaction (eg as triggers or as stored pprocs) and PostgreSQL
doesn't support nested transactions.

-- 
Tibor Laszlo
ltibor@mail.tiszanet.hu

Re: pl/pgSQL & transaction

From
Kovacs Zoltan Sandor
Date:
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

Re: pl/pgSQL & transaction

From
Cedar Cox
Date:

On Thu, 18 Jan 2001, Zolof 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 ????

You didn't read the documentation.

"It is important not to misunderstand the meaning of BEGIN/END for
grouping statements in PL/pgSQL and the database commands for transaction
control. Functions and trigger procedures cannot start or commit
transactions and Postgres does not have nested transactions. "