PL/pgSQL CURSOR support

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: PostgreSQL HACKERS <pgsql-hackers(at)postgreSQL(dot)org>
Subject: PL/pgSQL CURSOR support
Date: 2001-05-21 19:05:19
Message-ID: 200105211905.PAA14702@jupiter.jw.home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

#
# Note:
# The version with the attachment didn't make it up to now.
# Resending it without.
#

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Browse pgsql-hackers by date

  From Date Subject
Next Message Mikheev, Vadim 2001-05-21 20:01:29 RE: Plans for solving the VACUUM problem
Previous Message Bruce Momjian 2001-05-21 18:36:01 Re: AW: [HACKERS] Fix for tablename in targetlist