Thread: context in plpgsql functions

context in plpgsql functions

From
Gianni Mariani
Date:
I've been using PostgreSQL 7.3.2.

What, if any, way is there to share a context (global rowtype variable)
between plpgsql functions ?

Can row types be passed as parameters ?  (I seem to have had trouble
doing that)

e.g.

A transaction that looks like:

BEGIN;
select FindContext(5432);
select SomeRandomFunction('111');
COMMIT;


The functions would look somthing like:

CREATE or replace FUNCTION FindContext(int4) RETURNS int4 AS
'
DECLARE
    w_key alias for $1;
    w_context context%ROWTYPE;
BEGIN

    SELECT INTO w_context * FROM contecxt WHERE key = w_key;

    -- I want to access w_context in a different method

    RETURN 1;

END;
'
LANGUAGE 'plpgsql';


CREATE or replace FUNCTION SomeRandomFunction(int4) RETURNS int4 AS
'
DECLARE
    w_stuff alias for $1;
    w_context context%ROWTYPE; -- ??????????????
BEGIN

    update sometable set key = w_context.key where yadda = w_stuff;

    RETURN 1;

END;
'
LANGUAGE 'plpgsql';




Re: context in plpgsql functions

From
Ian Barwick
Date:
On Friday 16 May 2003 20:46, Gianni Mariani wrote:
> I've been using PostgreSQL 7.3.2.
>
> What, if any, way is there to share a context (global rowtype variable)
> between plpgsql functions ?

If you mean something like Oracle's PL/SQL packages, which can
contain package global variables, then unfortunately not.


Ian Barwick
barwick@gmx.net


Re: context in plpgsql functions

From
Jeff Eckermann
Date:
--- Ian Barwick <barwick@gmx.net> wrote:
> On Friday 16 May 2003 20:46, Gianni Mariani wrote:
> > I've been using PostgreSQL 7.3.2.
> >
> > What, if any, way is there to share a context
> (global rowtype variable)
> > between plpgsql functions ?
>
> If you mean something like Oracle's PL/SQL packages,
> which can
> contain package global variables, then unfortunately
> not.

I believe the usual workaround is to insert the values
in a table which has been created for that purpose.

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

Re: context in plpgsql functions

From
elein
Date:
You can do this with plpython.  There is a brief write up about
it at http://www.varlena.com/GeneralBits/23.html

There is one trick to it :-)

--elein

On Friday 16 May 2003 11:46, Gianni Mariani wrote:
> I've been using PostgreSQL 7.3.2.
>
> What, if any, way is there to share a context (global rowtype variable)
> between plpgsql functions ?
>
> Can row types be passed as parameters ?  (I seem to have had trouble
> doing that)
>
> e.g.
>
> A transaction that looks like:
>
> BEGIN;
> select FindContext(5432);
> select SomeRandomFunction('111');
> COMMIT;
>
>
> The functions would look somthing like:
>
> CREATE or replace FUNCTION FindContext(int4) RETURNS int4 AS
> '
> DECLARE
>     w_key alias for $1;
>     w_context context%ROWTYPE;
> BEGIN
>
>     SELECT INTO w_context * FROM contecxt WHERE key = w_key;
>
>     -- I want to access w_context in a different method
>
>     RETURN 1;
>
> END;
> '
> LANGUAGE 'plpgsql';
>
>
> CREATE or replace FUNCTION SomeRandomFunction(int4) RETURNS int4 AS
> '
> DECLARE
>     w_stuff alias for $1;
>     w_context context%ROWTYPE; -- ??????????????
> BEGIN
>
>     update sometable set key = w_context.key where yadda = w_stuff;
>
>     RETURN 1;
>
> END;
> '
> LANGUAGE 'plpgsql';
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

--
=============================================================
elein@varlena.com     Database Consulting     www.varlena.com
PostgreSQL General Bits    http:/www.varlena.com/GeneralBits/
   "Left foot, right foot, left foot, breathe.
   Right foot, left foot, right foot, breathe." --Father Tom


Re: context in plpgsql functions

From
Gianni Mariani
Date:
Jeff Eckermann wrote:

>--- Ian Barwick <barwick@gmx.net> wrote:
>
>
>>On Friday 16 May 2003 20:46, Gianni Mariani wrote:
>>
>>
>>>I've been using PostgreSQL 7.3.2.
>>>
>>>What, if any, way is there to share a context
>>>
>>>
>>(global rowtype variable)
>>
>>
>>>between plpgsql functions ?
>>>
>>>
>>If you mean something like Oracle's PL/SQL packages,
>>which can
>>contain package global variables, then unfortunately
>>not.
>>
>>
>
>I believe the usual workaround is to insert the values
>in a table which has been created for that purpose.
>
>
Yes - I was thinking of that - I was also thinking you could use a
cursor to do the job of pointing to the record in the table you care about.

However, when I try to do a MOVE cursor inside a plpgsql function i get :

    EXECUTE ''MOVE BACKWARD 1 IN "ContextCursor"''

    psql:kkk4:145: ERROR:  unexpected error -5 in EXECUTE of query 'MOVE
BACKWARD 1 IN "ContextCursor"'

or a

    PERFORM MOVE BACKWARD 1 IN "ContextCursor";

    psql:kkk4:145: ERROR:  parser: parse error at or near "BACKWARD" at
character 14

or

    MOVE BACKWARD 1 IN "ContextCursor";

    psql:kkk4:145: ERROR:  SPI_prepare() failed on "MOVE BACKWARD 1 IN
"ContextCursor""


.... yet that same statement works fine elsewhere ...

Ideas on how to move a cursor inside a plpgsql function ?

TIA



Re: context in plpgsql functions

From
Gianni Mariani
Date:
Gianni Mariani wrote:

> Jeff Eckermann wrote:
>
>> --- Ian Barwick <barwick@gmx.net> wrote:
>>
>>
>>> On Friday 16 May 2003 20:46, Gianni Mariani wrote:
>>>
>>>
>>>> I've been using PostgreSQL 7.3.2.
>>>>
>>>> What, if any, way is there to share a context
>>>>
>>>
>>> (global rowtype variable)
>>>
>>>
>>>> between plpgsql functions ?
>>>>
>>>
>>> If you mean something like Oracle's PL/SQL packages,
>>> which can
>>> contain package global variables, then unfortunately
>>> not.
>>>
>>
>>
>> I believe the usual workaround is to insert the values
>> in a table which has been created for that purpose.
>>
>>
> Yes - I was thinking of that - I was also thinking you could use a
> cursor to do the job of pointing to the record in the table you care
> about.
>
> However, when I try to do a MOVE cursor inside a plpgsql function i get :
>
>    EXECUTE ''MOVE BACKWARD 1 IN "ContextCursor"''
>
>    psql:kkk4:145: ERROR:  unexpected error -5 in EXECUTE of query
> 'MOVE BACKWARD 1 IN "ContextCursor"'
>
> or a
>      PERFORM MOVE BACKWARD 1 IN "ContextCursor";
>
>    psql:kkk4:145: ERROR:  parser: parse error at or near "BACKWARD" at
> character 14
>
> or
>
>    MOVE BACKWARD 1 IN "ContextCursor";
>
>    psql:kkk4:145: ERROR:  SPI_prepare() failed on "MOVE BACKWARD 1 IN
> "ContextCursor""
>
>
> .... yet that same statement works fine elsewhere ...
>
> Ideas on how to move a cursor inside a plpgsql function ?

OK - this one works.

CREATE FUNCTION MoveBack() RETURNS unknown AS '
    MOVE BACKWARD 1 FROM "ContextCursor";
    select ''A'';
' LANGUAGE SQL;

... then in the plpgsql function do this:

    PERFORM MoveBack();

It seems like there is a few issues with cursors and plpgsql functions.
Does anyone want some test cases ?

So it seems like it is possible to create a somewhat simple context
handler using cursors.  It's also good that with cursors by default
there is a scope by default in a transaction (the 7.4 doc seems to
suggest that you can declare cursors that live beyond a transaction).

Having said all that, it would be good if you could define a "class" of
plpgsql functions that could share contextual information implicitly.






Re: context in plpgsql functions

From
elein
Date:
You can do this in a C function or a plpython function
by saving out the information into context in C or
into the SD[] dictionary in plpython.

This is the subject of my talk at OSCON :-)

elein


On Saturday 17 May 2003 08:50, Gianni Mariani wrote:
> Gianni Mariani wrote:
>
> > Jeff Eckermann wrote:
> >
> >> --- Ian Barwick <barwick@gmx.net> wrote:
> >>
> >>
> >>> On Friday 16 May 2003 20:46, Gianni Mariani wrote:
> >>>
> >>>
> >>>> I've been using PostgreSQL 7.3.2.
> >>>>
> >>>> What, if any, way is there to share a context
> >>>>
> >>>
> >>> (global rowtype variable)
> >>>
> >>>
> >>>> between plpgsql functions ?
> >>>>
> >>>
> >>> If you mean something like Oracle's PL/SQL packages,
> >>> which can
> >>> contain package global variables, then unfortunately
> >>> not.
> >>>
> >>
> >>
> >> I believe the usual workaround is to insert the values
> >> in a table which has been created for that purpose.
> >>
> >>
> > Yes - I was thinking of that - I was also thinking you could use a
> > cursor to do the job of pointing to the record in the table you care
> > about.
> >
> > However, when I try to do a MOVE cursor inside a plpgsql function i get :
> >
> >    EXECUTE ''MOVE BACKWARD 1 IN "ContextCursor"''
> >
> >    psql:kkk4:145: ERROR:  unexpected error -5 in EXECUTE of query
> > 'MOVE BACKWARD 1 IN "ContextCursor"'
> >
> > or a
> >      PERFORM MOVE BACKWARD 1 IN "ContextCursor";
> >
> >    psql:kkk4:145: ERROR:  parser: parse error at or near "BACKWARD" at
> > character 14
> >
> > or
> >
> >    MOVE BACKWARD 1 IN "ContextCursor";
> >
> >    psql:kkk4:145: ERROR:  SPI_prepare() failed on "MOVE BACKWARD 1 IN
> > "ContextCursor""
> >
> >
> > .... yet that same statement works fine elsewhere ...
> >
> > Ideas on how to move a cursor inside a plpgsql function ?
>
> OK - this one works.
>
> CREATE FUNCTION MoveBack() RETURNS unknown AS '
>     MOVE BACKWARD 1 FROM "ContextCursor";
>     select ''A'';
> ' LANGUAGE SQL;
>
> ... then in the plpgsql function do this:
>
>     PERFORM MoveBack();
>
> It seems like there is a few issues with cursors and plpgsql functions.
> Does anyone want some test cases ?
>
> So it seems like it is possible to create a somewhat simple context
> handler using cursors.  It's also good that with cursors by default
> there is a scope by default in a transaction (the 7.4 doc seems to
> suggest that you can declare cursors that live beyond a transaction).
>
> Having said all that, it would be good if you could define a "class" of
> plpgsql functions that could share contextual information implicitly.
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>

--
=============================================================
elein@varlena.com     Database Consulting     www.varlena.com
PostgreSQL General Bits    http:/www.varlena.com/GeneralBits/
   "Free your mind the rest will follow" -- en vogue