From: otisg@ivillage.com
To: pgsql-sql@postgresql.org
Subject: Re: Err. compiling func. with SET TRANS...
Date: 27 Feb 2002 06:18:49 -0800
Message-ID: <20020227141849.16214.cpmta@c006.snv.cp.net> (raw)
On Tue, 26 February 2002, "Christopher Kings-Lynne" wrote:
>
> > CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> > BEGIN
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > BEGIN;
> > SELECT 1;
> > END;
> > RETURN 1;
> > END;
> > ' LANGUAGE 'plpgsql';
> >
> > This is as simple as it gets.
> > I think my syntax is correct (I checked Practical PostgreSQL book
> > as well as a number of 7.2 PDF documents, etc.).
> >
> > Am I missing a secret ingredient here?
>
> I'm no PL/PgSQL expert, but I think that you cannot do transactions within a
> function (this is because postgres doesn't support nested transactions.
>
> However, since the function will run inside a transaction anyway, just do
> this:
>
> CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> BEGIN
> SELECT 1;
> RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';
>
> Now, of course you can't do your isolated transaction, so you'll need to
> create the function above and then use it like this:
>
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SELECT simple_fun();
> COMMIT;
Hello Chris, thanks for the answer.
Ouch, what a workaround :(
Unfortunately, this doesn't seem to work either:
CREATE FUNCTION simple() RETURNS INTEGER AS '
BEGIN
RETURN 1;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION simple_wrap() RETURNS INTEGER AS '
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT simple();
COMMIT;
END;
' LANGUAGE 'plpgsql';
And this is the error:
=> select simple_wrap();
NOTICE: Error occurred while executing PL/pgSQL function simple_wrap
NOTICE: line 2 at SQL statement
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
Unfortunately, line numbers don't seem to be real line numbers, so I can't use them to find the error.
Any idea why the above doesn't work?
Thanks again,
Otis
_________________________________________________________________
iVillage.com: Solutions for Your Life
Check out the most exciting women's community on the Web
http://www.ivillage.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: otisg@ivillage.com
Subject: Re: Err. compiling func. with SET TRANS...
In-Reply-To: <20020227141849.16214.cpmta@c006.snv.cp.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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