Re: context in plpgsql functions

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.