PL/pgSQL CURSOR support - Mailing list pgsql-hackers

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
willmake up           a unique name inside the backend. A portal created by           this  can be accessed by the main
applicationas 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
FOREXECUTE <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
thevariable will be     set to that name at OPEN. If the function assigns  a  value     before OPEN, that'll be used as
theportal 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
anotherto     open  a  cursor,  assigning  it's   return   value   to   a     "refcursor",  pass  that  down to other
functionsand - 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 #