pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Err. compiling func. with SET TRANS...
3+ messages / 2 participants
[nested] [flat]

* Err. compiling func. with SET TRANS...
@ 2002-02-27 07:33 otisg@ivillage.com
  2002-02-27 07:54 ` Re: Err. compiling func. with SET TRANS... Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 3+ messages in thread

From: otisg@ivillage.com @ 2002-02-27 07:33 UTC (permalink / raw)
  To: pgsql-sql

Hello,

I'm having trouble getting functions with SET TRANSACTION ISOLATION ... to parse.

This is the error I'm getting (example function below the error):

=> select simple_fun();
NOTICE:  plpgsql: ERROR during compile of simple_fun near line 3
ERROR:  parse error at or near ";"

This is the simple_fun function that is causing the above error:

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?

Thank you,
Otis

_________________________________________________________________
iVillage.com: Solutions for Your Life 
Check out the most exciting women's community on the Web   
http://www.ivillage.com



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Err. compiling func. with SET TRANS...
  2002-02-27 07:33 Err. compiling func. with SET TRANS... otisg@ivillage.com
@ 2002-02-27 07:54 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 3+ messages in thread

From: Christopher Kings-Lynne @ 2002-02-27 07:54 UTC (permalink / raw)
  To: otisg@ivillage.com; pgsql-sql

> 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;

Chris




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Err. compiling func. with SET TRANS...
@ 2002-02-27 14:18 otisg@ivillage.com
  0 siblings, 0 replies; 3+ messages in thread

From: otisg@ivillage.com @ 2002-02-27 14:18 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-02-27 14:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-27 07:33 Err. compiling func. with SET TRANS... otisg@ivillage.com
2002-02-27 07:54 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-27 14:18 Re: Err. compiling func. with SET TRANS... otisg@ivillage.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