Re: context in plpgsql functions - Mailing list pgsql-general

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