Hello,
this patch adds scrollable support functions to SPI. It's necessary for
scrollable cursors in plpgsql. Original API is without changes.
Regards
Pavel Stehule
_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
*** ./doc/src/sgml/spi.sgml.orig 2007-01-14 12:37:19.000000000 +0100
--- ./doc/src/sgml/spi.sgml 2007-01-14 13:42:29.000000000 +0100
***************
*** 1344,1349 ****
--- 1344,1481 ----
<!-- *********************************************** -->
+ <refentry id="spi-spi-cursor-open-with-options">
+ <refmeta>
+ <refentrytitle>SPI_cursor_open_with_options</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_cursor_open_with_options</refname>
+ <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_cursor_open_with_options</primary></indexterm>
+
+ <refsynopsisdiv>
+ <synopsis>
+ Portal SPI_cursor_open_with_options(const char * <parameter>name</parameter>, void * <parameter>plan</parameter>,
+ Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
+ bool <parameter>read_only</parameter>, int cursorOptions)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_cursor_open_with_options</function> sets up a cursor (internally,
+ a portal) that will execute a plan prepared by
+ <function>SPI_prepare</function>. The parameters have the same
+ meanings as the corresponding parameters to
+ <function>SPI_execute_plan</function>. This function allows directly set cursor's
+ options.
+ </para>
+
+ <para>
+ Using a cursor instead of executing the plan directly has two
+ benefits. First, the result rows can be retrieved a few at a time,
+ avoiding memory overrun for queries that return many rows. Second,
+ a portal can outlive the current procedure (it can, in fact, live
+ to the end of the current transaction). Returning the portal name
+ to the procedure's caller provides a way of returning a row set as
+ result.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>name</parameter></literal></term>
+ <listitem>
+ <para>
+ name for portal, or <symbol>NULL</symbol> to let the system
+ select a name
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>void * <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Datum * <parameter>values</parameter></literal></term>
+ <listitem>
+ <para>
+ An array of actual parameter values. Must have same length as the
+ plan's number of arguments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>const char * <parameter>nulls</parameter></literal></term>
+ <listitem>
+ <para>
+ An array describing which parameters are null. Must have same length as
+ the plan's number of arguments.
+ <literal>n</literal> indicates a null value (entry in
+ <parameter>values</> will be ignored); a space indicates a
+ nonnull value (entry in <parameter>values</> is valid).
+ </para>
+
+ <para>
+ If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
+ <function>SPI_cursor_open</function> assumes that no parameters are
+ null.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool <parameter>read_only</parameter></literal></term>
+ <listitem>
+ <para>
+ <literal>true</> for read-only execution
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>cursorOptions</parameter></literal></term>
+ <listitem>
+ <para>This option allow to set cursor's options. It's one or combination of
+ <symbol>CURSOR_OPT_BINARY</symbol>,
+ <symbol>CURSOR_OPT_SCROLL</symbol>,
+ <symbol>CURSOR_OPT_NO_SCROLL</symbol>,
+ <symbol>CURSOR_OPT_INSENSITIVE</symbol>, or
+ <symbol>CURSOR_OPT_HOLD</symbol>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ pointer to portal containing the cursor, or <symbol>NULL</symbol>
+ on error
+ </para>
+ </refsect1>
+ </refentry>
+
+ <!-- *********************************************** -->
+
<refentry id="spi-spi-cursor-find">
<refmeta>
<refentrytitle>SPI_cursor_find</refentrytitle>
***************
*** 1472,1477 ****
--- 1604,1685 ----
<!-- *********************************************** -->
+ <refentry id="spi-spi-scroll-cursor-fetch">
+ <refmeta>
+ <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_scroll_cursor_fetch</refname>
+ <refpurpose>fetch some rows from a scrollable cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
+
+ <refsynopsisdiv>
+ <synopsis>
+ void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
+ cursor. This is equivalent to the SQL command <command>FETCH</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FetchDirection <parameter>forward</parameter></literal></term>
+ <listitem>
+ <para>
+ It's one from <symbol>FETCH_FORWARD</symbol>,
+ <symbol>FETCH_BACKWARD</symbol>,
+ <symbol>FETCH_ABSOLUTE</symbol> or
+ <symbol>FETCH_RELATIVE</symbol> values. Please, look to SQL command <command>FETCH</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to fetch.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+ </refentry>
+
+ <!-- *********************************************** -->
+
<refentry id="spi-spi-cursor-move">
<refmeta>
<refentrytitle>SPI_cursor_move</refentrytitle>
***************
*** 1517,1523 ****
<term><literal>bool <parameter>forward</parameter></literal></term>
<listitem>
<para>
! true for move forward, false for move backward
</para>
</listitem>
</varlistentry>
--- 1725,1798 ----
<term><literal>bool <parameter>forward</parameter></literal></term>
<listitem>
<para>
! true for fetch forward, false for fetch backward
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>long <parameter>count</parameter></literal></term>
! <listitem>
! <para>
! maximum number of rows to move
! </para>
! </listitem>
! </varlistentry>
! </variablelist>
! </refsect1>
! </refentry>
!
! <!-- *********************************************** -->
!
! <refentry id="spi-spi-scroll-cursor-move">
! <refmeta>
! <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
! </refmeta>
!
! <refnamediv>
! <refname>SPI_scroll_cursor_move</refname>
! <refpurpose>move a scrollable cursor</refpurpose>
! </refnamediv>
!
! <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
!
! <refsynopsisdiv>
! <synopsis>
! void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
! </synopsis>
! </refsynopsisdiv>
!
! <refsect1>
! <title>Description</title>
!
! <para>
! <function>SPI_scroll_cursor_move</function> skips over some number of rows
! in a cursor. This is equivalent to the SQL command
! <command>MOVE</>.
! </para>
! </refsect1>
!
! <refsect1>
! <title>Arguments</title>
!
! <variablelist>
! <varlistentry>
! <term><literal>Portal <parameter>portal</parameter></literal></term>
! <listitem>
! <para>
! portal containing the cursor
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>FetchDirection <parameter>forward</parameter></literal></term>
! <listitem>
! <para>
! It's one from <symbol>FETCH_FORWARD</symbol>,
! <symbol>FETCH_BACKWARD</symbol>,
! <symbol>FETCH_ABSOLUTE</symbol> or
! <symbol>FETCH_RELATIVE</symbol> values. Please, look to SQL command <command>FETCH</>.
</para>
</listitem>
</varlistentry>
*** ./src/backend/executor/spi.c.orig 2007-01-14 09:54:37.000000000 +0100
--- ./src/backend/executor/spi.c 2007-01-14 10:13:21.000000000 +0100
***************
*** 45,51 ****
static void _SPI_error_callback(void *arg);
! static void _SPI_cursor_operation(Portal portal, bool forward, long count,
DestReceiver *dest);
static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
--- 45,55 ----
static void _SPI_error_callback(void *arg);
! static Portal _SPI_cursor_open(const char *name, void *plan,
! Datum *Values, const char *Nulls,
! bool read_only, int *cursorOptions);
!
! static void _SPI_cursor_operation(Portal portal, FetchDirection direction, long count,
DestReceiver *dest);
static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
***************
*** 825,835 ****
--- 829,865 ----
*
* Open a prepared SPI plan as a portal
*/
+
Portal
SPI_cursor_open(const char *name, void *plan,
Datum *Values, const char *Nulls,
bool read_only)
{
+ return _SPI_cursor_open(name, plan, Values, Nulls, read_only, NULL);
+
+ }
+
+
+ /*
+ * SPI_cursor_open()
+ *
+ * Open a prepared SPI plan as a portal. Allow set cursor's options.
+ */
+
+ Portal
+ SPI_cursor_open_with_options(const char *name, void *plan,
+ Datum *Values, const char *Nulls,
+ bool read_only, int cursorOptions)
+ {
+ return _SPI_cursor_open(name, plan, Values, Nulls, read_only, &cursorOptions);
+ }
+
+
+ static Portal
+ _SPI_cursor_open(const char *name, void *plan,
+ Datum *Values, const char *Nulls,
+ bool read_only, int *cursorOptions)
+ {
_SPI_plan *spiplan = (_SPI_plan *) plan;
List *qtlist;
List *ptlist;
***************
*** 944,955 ****
/*
* Set up options for portal.
*/
! portal->cursorOptions &= ~(CURSOR_OPT_SCROLL | CURSOR_OPT_NO_SCROLL);
! if (list_length(ptlist) == 1 &&
! ExecSupportsBackwardScan((Plan *) linitial(ptlist)))
! portal->cursorOptions |= CURSOR_OPT_SCROLL;
else
! portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
/*
* Set up the snapshot to use. (PortalStart will do CopySnapshot, so we
--- 974,992 ----
/*
* Set up options for portal.
*/
!
! if (!cursorOptions)
! {
! /* if cursorOption are NULL, allow SCROLL if backward scan is possible */
! portal->cursorOptions &= ~(CURSOR_OPT_SCROLL | CURSOR_OPT_NO_SCROLL);
! if (list_length(ptlist) == 1 &&
! ExecSupportsBackwardScan((Plan *) linitial(ptlist)))
! portal->cursorOptions |= CURSOR_OPT_SCROLL;
! else
! portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
! }
else
! portal->cursorOptions = *cursorOptions;
/*
* Set up the snapshot to use. (PortalStart will do CopySnapshot, so we
***************
*** 988,993 ****
--- 1025,1057 ----
/*
+ * SPI_scroll_cursor_fetch()
+ *
+ * Fetch rows in a scrollable cursor
+ */
+ void
+ SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction, long count)
+ {
+ _SPI_cursor_operation(portal, direction, count,
+ CreateDestReceiver(DestSPI, NULL));
+ /* we know that the DestSPI receiver doesn't need a destroy call */
+ }
+
+
+ /*
+ * SPI_scroll_cursor_move()
+ *
+ * Move in a scarollable cursor
+ */
+ void
+ SPI_scroll_cursor_move(Portal portal, FetchDirection direction, long count)
+ {
+ _SPI_cursor_operation(portal, direction, count, None_Receiver);
+ }
+
+
+
+ /*
* SPI_cursor_fetch()
*
* Fetch rows in a cursor
***************
*** 995,1001 ****
void
SPI_cursor_fetch(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward, count,
CreateDestReceiver(DestSPI, NULL));
/* we know that the DestSPI receiver doesn't need a destroy call */
}
--- 1059,1065 ----
void
SPI_cursor_fetch(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward ? FETCH_FORWARD : FETCH_BACKWARD, count,
CreateDestReceiver(DestSPI, NULL));
/* we know that the DestSPI receiver doesn't need a destroy call */
}
***************
*** 1009,1015 ****
void
SPI_cursor_move(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward, count, None_Receiver);
}
--- 1073,1079 ----
void
SPI_cursor_move(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward ? FETCH_FORWARD : FETCH_BACKWARD, count, None_Receiver);
}
***************
*** 1663,1669 ****
* Do a FETCH or MOVE in a cursor
*/
static void
! _SPI_cursor_operation(Portal portal, bool forward, long count,
DestReceiver *dest)
{
long nfetched;
--- 1727,1733 ----
* Do a FETCH or MOVE in a cursor
*/
static void
! _SPI_cursor_operation(Portal portal, FetchDirection direction, long count,
DestReceiver *dest)
{
long nfetched;
***************
*** 1684,1690 ****
/* Run the cursor */
nfetched = PortalRunFetch(portal,
! forward ? FETCH_FORWARD : FETCH_BACKWARD,
count,
dest);
--- 1748,1754 ----
/* Run the cursor */
nfetched = PortalRunFetch(portal,
! direction,
count,
dest);
*** ./src/include/executor/spi.h.orig 2007-01-14 09:24:00.000000000 +0100
--- ./src/include/executor/spi.h 2007-01-14 09:36:43.000000000 +0100
***************
*** 25,30 ****
--- 25,31 ----
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "executor/executor.h"
+ #include "nodes/parsenodes.h"
#include "nodes/execnodes.h"
#include "nodes/params.h"
#include "nodes/plannodes.h"
***************
*** 125,133 ****
--- 126,138 ----
extern Portal SPI_cursor_open(const char *name, void *plan,
Datum *Values, const char *Nulls, bool read_only);
+ extern Portal SPI_cursor_open_with_options(const char *name, void *plan,
+ Datum *values, const char *Nulls, bool read_only, int cursorOptions);
extern Portal SPI_cursor_find(const char *name);
extern void SPI_cursor_fetch(Portal portal, bool forward, long count);
extern void SPI_cursor_move(Portal portal, bool forward, long count);
+ extern void SPI_scroll_cursor_fetch(Portal, FetchDirection direction, long count);
+ extern void SPI_scroll_cursor_move(Portal, FetchDirection direction, long count);
extern void SPI_cursor_close(Portal portal);
extern void AtEOXact_SPI(bool isCommit);
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
