Thread: context in plpgsql functions
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';
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
--- 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
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
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
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.
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