Re: context in plpgsql functions

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(at)gmx(dot)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(at)varlena(dot)com Database Consulting www.varlena.com
PostgreSQL General Bits http:/www.varlena.com/GeneralBits/
"Free your mind the rest will follow" -- en vogue