pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPl/pgSQL & TRANSACTION
6+ messages / 5 participants
[nested] [flat]
* Pl/pgSQL & TRANSACTION
@ 2001-01-18 17:16 Zolof <zolof@club-internet.fr>
0 siblings, 1 reply; 6+ messages in thread
From: Zolof @ 2001-01-18 17:16 UTC (permalink / raw)
To: pgsql-docs
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] 6+ messages in thread
* pl/pgSQL & transaction
@ 2001-01-18 17:17 Zolof <zolof@club-internet.fr>
0 siblings, 2 replies; 6+ 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] 6+ messages in thread
* Re: Pl/pgSQL & TRANSACTION
@ 2001-01-19 01:51 Robert B. Easter <reaster@comptechnews.com>
parent: Zolof <zolof@club-internet.fr>
0 siblings, 0 replies; 6+ messages in thread
From: Robert B. Easter @ 2001-01-19 01:51 UTC (permalink / raw)
To: Zolof <zolof@club-internet.fr>; pgsql-docs
You'll have to begin the transaction before the function is run (implicit
within a query if not in a xact). End the transaction after the function
returns. If the function does a RAISE EXCEPTION ''I don't like that'', then
the transaction is aborted during the function. Again, within a query (which
triggers your function), a transaction is begun implicity if a transaction is
not begun explicity beforehand with BEGIN.
On Thursday 18 January 2001 12:16, 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 ????
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pl/pgSQL & transaction
@ 2001-01-19 10:24 Tibor Laszlo <ltibor@mail.tiszanet.hu>
parent: Zolof <zolof@club-internet.fr>
1 sibling, 1 reply; 6+ messages in thread
From: Tibor Laszlo @ 2001-01-19 10:24 UTC (permalink / raw)
To: pgsql-interfaces@postgresql.org; +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] 6+ messages in thread
* Re: [INTERFACES] pl/pgSQL & transaction
@ 2001-01-19 11:41 Cedar Cox <cedarc@visionforisrael.com>
parent: Zolof <zolof@club-internet.fr>
1 sibling, 0 replies; 6+ messages in thread
From: Cedar Cox @ 2001-01-19 11:41 UTC (permalink / raw)
To: Zolof <zolof@club-internet.fr>; +Cc: pgsql-docs; pgsql-general@postgresql.org; pgsql-interfaces@postgresql.org; 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] 6+ messages in thread
* Re: pl/pgSQL & transaction
@ 2001-01-20 12:19 Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
parent: Tibor Laszlo <ltibor@mail.tiszanet.hu>
0 siblings, 0 replies; 6+ 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] 6+ messages in thread
end of thread, other threads:[~2001-01-20 12:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-18 17:16 Pl/pgSQL & TRANSACTION Zolof <zolof@club-internet.fr>
2001-01-19 01:51 ` Robert B. Easter <reaster@comptechnews.com>
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 ` Re: pl/pgSQL & transaction Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2001-01-19 11:41 ` Re: [INTERFACES] pl/pgSQL & transaction 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