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 #