Received: from c006.snv.cp.net (c006-h002.c006.snv.cp.net [209.228.33.158]) by postgresql.org (Postfix) with SMTP id B516F475A02 for ; Wed, 27 Feb 2002 09:19:03 -0500 (EST) Received: (cpmta 16215 invoked from network); 27 Feb 2002 06:18:49 -0800 Date: 27 Feb 2002 06:18:49 -0800 Message-ID: <20020227141849.16214.cpmta@c006.snv.cp.net> X-Sent: 27 Feb 2002 14:18:49 GMT Received: from [66.108.148.98] by mail.ivillage.com with HTTP; 27 Feb 2002 06:18:49 PST Content-Type: text/plain Content-Disposition: inline Mime-Version: 1.0 To: pgsql-sql@postgresql.org From: otisg@ivillage.com X-Mailer: Web Mail 3.9.3.5 X-Sent-From: otisg@ivillage.com Subject: Re: Err. compiling func. with SET TRANS... X-Archive-Number: 200202/431 X-Sequence-Number: 6852 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