Re: Help with SET TRANSACTION in a function

On Mon, 4 Mar 2002, otisg wrote:

> Hello,
> > From: Stephan Szabo
> > On 28 Feb 2002, OtisUsenet wrote:
> >
> > I was wondering if anyone here can help. I could not get any help on
> > pgsql-sql nor pgsql-general mailing lists.
> > >
> > I'm having trouble getting functions with SET TRANSACTION ISOLATION
> > ... to parse.
> >
> > Without nested transactions, you're not going to have any luck I
> think.
> > First of all the extra begin/end in the function are not allowed and
> > IIRC, set transaction isolation level must be the first statement of
> the
> > transaction which it won't be since the enclosing transaction will
> have
> > started the select simple_fun().
>
> Hello,
>
> If I understand it correctly the function should then look like this:
>
> CREATE FUNCTION simple_fun() RETURNS INTEGER AS '
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN
> RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';

Only declarations go outside the begin. As far as I can see, you
really just can't put a set transaction isolation level in a function
and expect it to work. If PostgreSQL had nested transactions you could
presumably do it then, but it doesn't.