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

Reply via email to