pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedpl/pgSQL & transaction
4+ messages / 4 participants
[nested] [flat]
* pl/pgSQL & transaction
@ 2001-01-18 17:17 Zolof <zolof@club-internet.fr>
2001-01-19 10:24 ` Re: pl/pgSQL & transaction Tibor Laszlo <ltibor@mail.tiszanet.hu>
2001-01-19 11:41 ` Re: [INTERFACES] pl/pgSQL & transaction Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 2 replies; 4+ messages in thread
From: Zolof @ 2001-01-18 17:17 UTC (permalink / raw)
To: pgsql-docs@postgresql.org.pgsql-general
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 ????
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pl/pgSQL & transaction
2001-01-18 17:17 pl/pgSQL & transaction Zolof <zolof@club-internet.fr>
@ 2001-01-19 10:24 ` Tibor Laszlo <ltibor@mail.tiszanet.hu>
2001-01-20 12:19 ` Re: pl/pgSQL & transaction Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
1 sibling, 1 reply; 4+ messages in thread
From: Tibor Laszlo @ 2001-01-19 10:24 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Zolof <zolof@club-internet.fr>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pl/pgSQL & transaction
2001-01-18 17:17 pl/pgSQL & transaction Zolof <zolof@club-internet.fr>
2001-01-19 10:24 ` Re: pl/pgSQL & transaction Tibor Laszlo <ltibor@mail.tiszanet.hu>
@ 2001-01-20 12:19 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 0 replies; 4+ messages in thread
From: Kovacs Zoltan Sandor @ 2001-01-20 12:19 UTC (permalink / raw)
To: Tibor Laszlo <ltibor@mail.tiszanet.hu>; +Cc: pgsql-interfaces@postgresql.org, Zolof <zolof@club-internet.fr>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] pl/pgSQL & transaction
2001-01-18 17:17 pl/pgSQL & transaction Zolof <zolof@club-internet.fr>
@ 2001-01-19 11:41 ` Cedar Cox <cedarc@visionforisrael.com>
1 sibling, 0 replies; 4+ messages in thread
From: Cedar Cox @ 2001-01-19 11:41 UTC (permalink / raw)
To: Zolof <zolof@club-internet.fr>; +Cc: pgsql-docs@postgresql.org; pgsql-general@postgresql.org; pgsql-interfaces; pgsql-sql@postgresql.org
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. "
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-01-20 12:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-18 17:17 pl/pgSQL & transaction Zolof <zolof@club-internet.fr>
2001-01-19 10:24 ` Tibor Laszlo <ltibor@mail.tiszanet.hu>
2001-01-20 12:19 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2001-01-19 11:41 ` Cedar Cox <cedarc@visionforisrael.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox