Hi folks,

    I  just  committed  changes  to the SPI manager and PL/pgSQL,
    providing full CURSOR  support.  A  detailed  description  is
    attached as a Postscript file. Brief description follows.

    Enhancement of SPI:

      There are a couple of new functions and internal changes to
      the SPI memory management. SPI now creates separate  memory
      contexts  for  prepared  and  saved  plans and tuple result
      sets. The contexts are children of  where  the  allocations
      used  to  happen,  so  it's  fully  upgrade compatible. New
      functions SPI_freeplan(plan)  and  SPI_freetuptable(tuptab)
      allow to simply destroy the contexts when no longer needed.

      The other new functions deal with portals:

          Portal
          SPI_cursor_find(char *name);

            Get an existing portal by name

          Portal
          SPI_cursor_open(char *name, void *plan,
                          Datum *Values, char *Nulls);

            Use a prepared or saved SPI  plan  to  create  a  new
            portal.  if <name> is NULL, the function will make up
            a unique name inside the backend. A portal created by
            this  can be accessed by the main application as well
            if SPI_cursor_open() was called inside of an explicit
            transaction block.

          void
          SPI_cursor_fetch(Portal portal, bool forward, int count);

            Fetch  at  max  <count> tuples from <portal> into the
            well  known  SPI_tuptable  and   set   SPI_processed.
            <portal>  could  be  any  existing  portal,  even one
            created by the main  application  using  DECLARE  ...
            CURSOR.

          void
          SPI_cursor_move(Portal portal, bool forward, int count);

            Same as fetch but suppress tuples.

          void
          SPI_cursor_close(Portal portal);

            Close the given portal. Doesn't matter who created it
            (SPI or main application).

    New datatype "refcursor"

      A new datatype "refcursor" is created as a basetype,  which
      is equivalent to "text". This is required below.

    Enhancement of PL/pgSQL

      Explicit cursor can be declared as:

          DECLARE
              ...
              curname CURSOR [(argname type [, ...])]
                      IS <select_stmt>;
              ...

      The  <select_stmt>  can use any so far declared variable or
      positional function  arguments  (possibly  aliased).  These
      will be evaluated at OPEN time.

      Explicit cursor can be opened with:

          BEGIN
              ...
              OPEN curname [(expr [, ...])];
              ...

      The expression list is required if and only if the explicit
      cursor declaration contains an argument list.  The  created
      portal  will be named 'curname' and is accessible globally.

      Reference cursor can be declared as:

          DECLARE
              ...
              varname REFCURSOR;
              ...

      and opened with

          BEGIN
              ...
              OPEN varname FOR <select_stmt>;
              -- or
              OPEN varname FOR EXECUTE <string expression>;
              ...

      The type "refcursor" is  a  datatype  like  text,  and  the
      variables   "value"   controls   the   "name"  argument  to
      SPI_cursor_open(). Defaulting to NULL, the resulting portal
      will  get  a  generic, unique name and the variable will be
      set to that name at OPEN. If the function assigns  a  value
      before OPEN, that'll be used as the portal name.

      Cursors (of both types) are used with:

          BEGIN
              ...
              FETCH cursorvar INTO {record | row | var [, ...]};
              ...
              CLOSE cursorvar;

      FETCH sets the global variable FOUND to flag if another row
      is available.  A typical loop thus looks like this:

          BEGIN
              OPEN myrefcur FOR SELECT * FROM mytab;
              LOOP
                  FETCH myrefcur INTO myrow;
                  EXIT WHEN NOT FOUND;
               -- Process one row
              END LOOP;
              CLOSE myrefcur;

      The "refcursor" type can be used for function arguments  or
      return  values as well. So one function can call another to
      open  a  cursor,  assigning  it's   return   value   to   a
      "refcursor",  pass  that  down to other functions and - you
      get the idea.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
Mon, 21 May 2001 11:06:13 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
PL/pgSQL CURSOR support