This is the most recent email I have on this.  Was the scrollable patch
applied?  If not, would you resubmit?


I resubmit scrollable cursor patch

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig	2007-01-26 20:30:17.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml	2007-01-26 21:33:38.000000000 +0100
***************
*** 2354,2360 ****
      internally to avoid memory problems.) A more interesting usage is to
      return a reference to a cursor that a function has created, allowing the
      caller to read the rows. This provides an efficient way to return
!     large row sets from functions.
     </para>
  
     <sect2 id="plpgsql-cursor-declarations">
--- 2354,2361 ----
      internally to avoid memory problems.) A more interesting usage is to
      return a reference to a cursor that a function has created, allowing the
      caller to read the rows. This provides an efficient way to return
!     large row sets from functions. PL/pgSQL allows to use scrollable 
!     cursors.
     </para>
  
     <sect2 id="plpgsql-cursor-declarations">
***************
*** 2368,2374 ****
       Another way is to use the cursor declaration syntax,
       which in general is:
  <synopsis>
! <replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
  </synopsis>
       (<literal>FOR</> may be replaced by <literal>IS</> for
       <productname>Oracle</productname> compatibility.)
--- 2369,2375 ----
       Another way is to use the cursor declaration syntax,
       which in general is:
  <synopsis>
! <replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
  </synopsis>
       (<literal>FOR</> may be replaced by <literal>IS</> for
       <productname>Oracle</productname> compatibility.)
***************
*** 2517,2523 ****
       <title><literal>FETCH</></title>
  
  <synopsis>
! FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
  </synopsis>
  
           <para>
--- 2518,2524 ----
       <title><literal>FETCH</></title>
  
  <synopsis>
! FETCH <optional> <replaceable>direction</replaceable> FROM </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
  </synopsis>
  
           <para>
***************
*** 2526,2539 ****
            variable, or a comma-separated list of simple variables, just like
            <command>SELECT INTO</command>.  As with <command>SELECT
             INTO</command>, the special variable <literal>FOUND</literal> may
!           be checked to see whether a row was obtained or not.
           </para>
- 
      <para>
       An example:
  <programlisting>
  FETCH curs1 INTO rowvar;
  FETCH curs2 INTO foo, bar, baz;
  </programlisting>
         </para>
       </sect3>
--- 2527,2545 ----
            variable, or a comma-separated list of simple variables, just like
            <command>SELECT INTO</command>.  As with <command>SELECT
             INTO</command>, the special variable <literal>FOUND</literal> may
!           be checked to see whether a row was obtained or not. More details
! 	  about <replaceable>direction</replaceable> you can find in
!           <xref linkend="sql-fetch"> without <literal>BACKWARD</> and <literal>FORWARD</> keywords.
! 	  Statement <command>FETCH</command> in <application>PL/pgSQL</> returns only one 
! 	  or zero row every time.
           </para>
      <para>
       An example:
  <programlisting>
  FETCH curs1 INTO rowvar;
  FETCH curs2 INTO foo, bar, baz;
+ FETCH LAST INTO x, y;
+ FETCH RELATIVE -2 INTO x;
  </programlisting>
         </para>
       </sect3>
*** ./doc/src/sgml/spi.sgml.orig	2007-01-14 12:37:19.000000000 +0100
--- ./doc/src/sgml/spi.sgml	2007-01-26 11:46:18.000000000 +0100
***************
*** 800,805 ****
--- 800,937 ----
  
  <!-- *********************************************** -->
  
+ <refentry id="spi-spi-prepare-cursor">
+  <refmeta>
+   <refentrytitle>SPI_prepare_cursor</refentrytitle>
+  </refmeta>
+ 
+  <refnamediv>
+   <refname>SPI_prepare_cursor</refname>
+   <refpurpose>prepare a plan for a cursor, without executing it yet</refpurpose>
+  </refnamediv>
+ 
+  <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
+ 
+  <refsynopsisdiv>
+ <synopsis>
+ void * SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>, int <parameter>options</parameter>)
+ </synopsis>
+  </refsynopsisdiv>
+ 
+  <refsect1>
+   <title>Description</title>
+ 
+   <para>
+    <function>SPI_prepare_cursor</function> creates and returns an execution
+    plan for the specified select but doesn't execute the command.
+    This function should only be called from a connected procedure. This
+    function allows set cursor's options. 
+   </para>
+ 
+   <para>
+    When the same or a similar command is to be executed repeatedly, it
+    may be advantageous to perform the planning only once.
+    <function>SPI_prepare_cursor</function> converts a command string into an
+    execution plan that can be executed repeatedly using
+    <function>SPI_execute_plan</function>.
+   </para>
+ 
+   <para>
+    A prepared command can be generalized by writing parameters
+    (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
+    constants in a normal command.  The actual values of the parameters
+    are then specified when <function>SPI_execute_plan</function> is called.
+    This allows the prepared command to be used over a wider range of
+    situations than would be possible without parameters.
+   </para>
+ 
+   <para>
+    The plan returned by <function>SPI_prepare_cursor</function> can be used
+    only in the current invocation of the procedure, since
+    <function>SPI_finish</function> frees memory allocated for a plan.
+    But a plan can be saved for longer using the function
+    <function>SPI_saveplan</function>.
+   </para>
+  </refsect1>
+ 
+  <refsect1>
+   <title>Arguments</title>
+ 
+   <variablelist>
+    <varlistentry>
+     <term><literal>const char * <parameter>command</parameter></literal></term>
+     <listitem>
+      <para>
+       command string
+      </para>
+     </listitem>
+    </varlistentry>
+ 
+    <varlistentry>
+     <term><literal>int <parameter>nargs</parameter></literal></term>
+     <listitem>
+      <para>
+       number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
+      </para>
+     </listitem>
+    </varlistentry>
+ 
+    <varlistentry>
+     <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
+     <listitem>
+      <para>
+       pointer to an array containing the <acronym>OID</acronym>s of
+       the data types of the parameters
+      </para>
+     </listitem>
+    </varlistentry>
+  
+    <varlistentry>
+     <term><literal>int <parameter>options</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>. Currently only <symbol>CURSOR_OPT_SCROLL</symbol>
+ 	is supported and allowed.
+      </para>
+     </listitem>
+    </varlistentry>
+   </variablelist>
+  </refsect1>
+ 
+  <refsect1>
+   <title>Return Value</title>
+ 
+   <para>
+    <function>SPI_prepare_cursor</function> returns a non-null pointer to an
+    execution plan.  On error, <symbol>NULL</symbol> will be returned,
+    and <varname>SPI_result</varname> will be set to one of the same
+    error codes used by <function>SPI_execute</function>, except that
+    it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
+    <parameter>command</parameter> is <symbol>NULL</symbol>, or if
+    <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
+    greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>, 
+    or if are used any unsupported <parameter>options</parameter>.
+   </para>
+  </refsect1>
+ 
+  <refsect1>
+   <title>Notes</title>
+ 
+   <para>
+    There is a disadvantage to using parameters: since the planner does
+    not know the values that will be supplied for the parameters, it
+    may make worse planning choices than it would make for a normal
+    command with all constants visible.
+   </para>
+  </refsect1>
+ </refentry>
+ 
+ <!-- *********************************************** -->
+ 
  <refentry id="spi-spi-getargcount">
   <refmeta>
    <refentrytitle>SPI_getargcount</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/commands/prepare.c.orig	2007-01-23 22:35:33.000000000 +0100
--- ./src/backend/commands/prepare.c	2007-01-26 19:07:21.000000000 +0100
***************
*** 105,111 ****
  	query_list = QueryRewrite(query);
  
  	/* Generate plans for queries.	Snapshot is already set. */
! 	plan_list = pg_plan_queries(query_list, NULL, false);
  
  	/*
  	 * Save the results.  We don't have the query string for this PREPARE, but
--- 105,111 ----
  	query_list = QueryRewrite(query);
  
  	/* Generate plans for queries.	Snapshot is already set. */
! 	plan_list = pg_plan_queries(query_list, NULL, false, false, 0);
  
  	/*
  	 * Save the results.  We don't have the query string for this PREPARE, but
*** ./src/backend/executor/functions.c.orig	2007-01-23 22:36:52.000000000 +0100
--- ./src/backend/executor/functions.c	2007-01-26 19:02:43.000000000 +0100
***************
*** 124,130 ****
  					 errmsg("%s is not allowed in a non-volatile function",
  							CreateQueryTag(queryTree))));
  
! 		planTree = pg_plan_query(queryTree, NULL);
  
  		newes = (execution_state *) palloc(sizeof(execution_state));
  		if (preves)
--- 124,130 ----
  					 errmsg("%s is not allowed in a non-volatile function",
  							CreateQueryTag(queryTree))));
  
! 		planTree = pg_plan_query(queryTree, NULL, false, 0);
  
  		newes = (execution_state *) palloc(sizeof(execution_state));
  		if (preves)
*** ./src/backend/executor/spi.c.orig	2007-01-14 09:54:37.000000000 +0100
--- ./src/backend/executor/spi.c	2007-01-26 19:21:44.000000000 +0100
***************
*** 34,40 ****
  static int	_SPI_connected = -1;
  static int	_SPI_curid = -1;
  
! static void _SPI_prepare_plan(const char *src, _SPI_plan *plan);
  
  static int _SPI_execute_plan(_SPI_plan *plan,
  				  Datum *Values, const char *Nulls,
--- 34,40 ----
  static int	_SPI_connected = -1;
  static int	_SPI_curid = -1;
  
! static void _SPI_prepare_plan(const char *src, _SPI_plan *plan, bool isCursor, int options);
  
  static int _SPI_execute_plan(_SPI_plan *plan,
  				  Datum *Values, const char *Nulls,
***************
*** 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,51 ----
  
  static void _SPI_error_callback(void *arg);
  
! static void _SPI_cursor_operation(Portal portal, FetchDirection direction, long count,
  					  DestReceiver *dest);
  
  static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
***************
*** 311,317 ****
  	plan.nargs = 0;
  	plan.argtypes = NULL;
  
! 	_SPI_prepare_plan(src, &plan);
  
  	res = _SPI_execute_plan(&plan, NULL, NULL,
  							InvalidSnapshot, InvalidSnapshot,
--- 311,317 ----
  	plan.nargs = 0;
  	plan.argtypes = NULL;
  
! 	_SPI_prepare_plan(src, &plan, false, 0);
  
  	res = _SPI_execute_plan(&plan, NULL, NULL,
  							InvalidSnapshot, InvalidSnapshot,
***************
*** 418,424 ****
  	plan.nargs = nargs;
  	plan.argtypes = argtypes;
  
! 	_SPI_prepare_plan(src, &plan);
  
  	/* copy plan to procedure context */
  	result = _SPI_copy_plan(&plan, _SPI_CPLAN_PROCXT);
--- 418,424 ----
  	plan.nargs = nargs;
  	plan.argtypes = argtypes;
  
! 	_SPI_prepare_plan(src, &plan, false, 0);
  
  	/* copy plan to procedure context */
  	result = _SPI_copy_plan(&plan, _SPI_CPLAN_PROCXT);
***************
*** 428,433 ****
--- 428,473 ----
  	return (void *) result;
  }
  
+ 
+ void *
+ SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, int options)
+ {
+ 	_SPI_plan	plan;
+ 	_SPI_plan  *result;
+ 
+ 	if (src == NULL || nargs < 0 || (nargs > 0 && argtypes == NULL))
+ 	{
+ 		SPI_result = SPI_ERROR_ARGUMENT;
+ 		return NULL;
+ 	}
+ 
+ 	/* only scrollable cursors are supported currently */
+ 	if (options != CURSOR_OPT_SCROLL && options != CURSOR_OPT_NO_SCROLL)
+ 	{
+ 		SPI_result = SPI_ERROR_ARGUMENT;
+ 		return NULL;
+ 	}
+ 
+ 	SPI_result = _SPI_begin_call(true);
+ 	if (SPI_result < 0)
+ 		return NULL;
+ 
+ 	plan.plancxt = NULL;		/* doesn't have own context */
+ 	plan.query = src;
+ 	plan.nargs = nargs;
+ 	plan.argtypes = argtypes;
+ 
+ 	_SPI_prepare_plan(src, &plan, true, options);
+ 
+ 	/* copy plan to procedure context */
+ 	result = _SPI_copy_plan(&plan, _SPI_CPLAN_PROCXT);
+ 
+ 	_SPI_end_call(true);
+ 
+ 	return (void *) result;
+ }
+ 
+ 
  void *
  SPI_saveplan(void *plan)
  {
***************
*** 824,830 ****
   * SPI_cursor_open()
   *
   *	Open a prepared SPI plan as a portal
!  */
  Portal
  SPI_cursor_open(const char *name, void *plan,
  				Datum *Values, const char *Nulls,
--- 864,870 ----
   * SPI_cursor_open()
   *
   *	Open a prepared SPI plan as a portal
!  */ 
  Portal
  SPI_cursor_open(const char *name, void *plan,
  				Datum *Values, const char *Nulls,
***************
*** 988,993 ****
--- 1028,1060 ----
  
  
  /*
+  * 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 */
  }
--- 1062,1068 ----
  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);
  }
  
  
--- 1076,1082 ----
  void
  SPI_cursor_move(Portal portal, bool forward, long count)
  {
! 	_SPI_cursor_operation(portal,  forward ? FETCH_FORWARD : FETCH_BACKWARD, count, None_Receiver);
  }
  
  
***************
*** 1260,1266 ****
   * Query and plan lists are stored into *plan.
   */
  static void
! _SPI_prepare_plan(const char *src, _SPI_plan *plan)
  {
  	List	   *raw_parsetree_list;
  	List	   *query_list_list;
--- 1327,1333 ----
   * Query and plan lists are stored into *plan.
   */
  static void
! _SPI_prepare_plan(const char *src, _SPI_plan *plan, bool isCursor, int options)
  {
  	List	   *raw_parsetree_list;
  	List	   *query_list_list;
***************
*** 1311,1317 ****
  		query_list_list = lappend(query_list_list, query_list);
  
  		plan_list = list_concat(plan_list,
! 								pg_plan_queries(query_list, NULL, false));
  	}
  
  	plan->qtlist = query_list_list;
--- 1378,1384 ----
  		query_list_list = lappend(query_list_list, query_list);
  
  		plan_list = list_concat(plan_list,
! 								pg_plan_queries(query_list, NULL, false, isCursor, options));
  	}
  
  	plan->qtlist = query_list_list;
***************
*** 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;
--- 1730,1736 ----
   *	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);
  
--- 1751,1757 ----
  
  	/* Run the cursor */
  	nfetched = PortalRunFetch(portal,
! 							  direction,
  							  count,
  							  dest);
  
*** ./src/backend/tcop/postgres.c.orig	2007-01-23 22:31:50.000000000 +0100
--- ./src/backend/tcop/postgres.c	2007-01-26 19:08:15.000000000 +0100
***************
*** 647,653 ****
  
  /* Generate a plan for a single already-rewritten query. */
  Plan *
! pg_plan_query(Query *querytree, ParamListInfo boundParams)
  {
  	Plan	   *plan;
  
--- 647,653 ----
  
  /* Generate a plan for a single already-rewritten query. */
  Plan *
! pg_plan_query(Query *querytree, ParamListInfo boundParams, bool isCursor, int options)
  {
  	Plan	   *plan;
  
***************
*** 659,665 ****
  		ResetUsage();
  
  	/* call the optimizer */
! 	plan = planner(querytree, false, 0, boundParams);
  
  	if (log_planner_stats)
  		ShowUsage("PLANNER STATISTICS");
--- 659,665 ----
  		ResetUsage();
  
  	/* call the optimizer */
! 	plan = planner(querytree, isCursor, options, boundParams);
  
  	if (log_planner_stats)
  		ShowUsage("PLANNER STATISTICS");
***************
*** 705,711 ****
   */
  List *
  pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! 				bool needSnapshot)
  {
  	List	   *plan_list = NIL;
  	ListCell   *query_list;
--- 705,711 ----
   */
  List *
  pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! 				bool needSnapshot, bool isCursor, int options)
  {
  	List	   *plan_list = NIL;
  	ListCell   *query_list;
***************
*** 727,733 ****
  				ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
  				needSnapshot = false;
  			}
! 			plan = pg_plan_query(query, boundParams);
  		}
  
  		plan_list = lappend(plan_list, plan);
--- 727,733 ----
  				ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
  				needSnapshot = false;
  			}
! 			plan = pg_plan_query(query, boundParams, isCursor, options);
  		}
  
  		plan_list = lappend(plan_list, plan);
***************
*** 875,881 ****
  		querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
  												NULL, 0);
  
! 		plantree_list = pg_plan_queries(querytree_list, NULL, true);
  
  		/* If we got a cancel signal in analysis or planning, quit */
  		CHECK_FOR_INTERRUPTS();
--- 875,881 ----
  		querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
  												NULL, 0);
  
! 		plantree_list = pg_plan_queries(querytree_list, NULL, true, false, 0);
  
  		/* If we got a cancel signal in analysis or planning, quit */
  		CHECK_FOR_INTERRUPTS();
***************
*** 1191,1197 ****
  		if (!is_named && numParams > 0)
  			plantree_list = NIL;
  		else
! 			plantree_list = pg_plan_queries(querytree_list, NULL, true);
  	}
  	else
  	{
--- 1191,1197 ----
  		if (!is_named && numParams > 0)
  			plantree_list = NIL;
  		else
! 			plantree_list = pg_plan_queries(querytree_list, NULL, true, false, 0);
  	}
  	else
  	{
***************
*** 1575,1581 ****
  		qContext = PortalGetHeapMemory(portal);
  		oldContext = MemoryContextSwitchTo(qContext);
  		query_list = copyObject(pstmt->query_list);
! 		plan_list = pg_plan_queries(query_list, params, true);
  		MemoryContextSwitchTo(oldContext);
  	}
  	else
--- 1575,1581 ----
  		qContext = PortalGetHeapMemory(portal);
  		oldContext = MemoryContextSwitchTo(qContext);
  		query_list = copyObject(pstmt->query_list);
! 		plan_list = pg_plan_queries(query_list, params, true, false, 0);
  		MemoryContextSwitchTo(oldContext);
  	}
  	else
*** ./src/include/executor/spi.h.orig	2007-01-14 09:24:00.000000000 +0100
--- ./src/include/executor/spi.h	2007-01-23 22:44:30.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"
***************
*** 97,102 ****
--- 98,104 ----
  					 Snapshot crosscheck_snapshot,
  					 bool read_only, long tcount);
  extern void *SPI_prepare(const char *src, int nargs, Oid *argtypes);
+ extern void * SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, int options);
  extern void *SPI_saveplan(void *plan);
  extern int	SPI_freeplan(void *plan);
  
***************
*** 128,133 ****
--- 130,137 ----
  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);
*** ./src/include/tcop/tcopprot.h.orig	2007-01-23 22:30:42.000000000 +0100
--- ./src/include/tcop/tcopprot.h	2007-01-26 19:01:53.000000000 +0100
***************
*** 50,58 ****
  extern List *pg_parse_query(const char *query_string);
  extern List *pg_analyze_and_rewrite(Node *parsetree, const char *query_string,
  					   Oid *paramTypes, int numParams);
! extern Plan *pg_plan_query(Query *querytree, ParamListInfo boundParams);
  extern List *pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! 				bool needSnapshot);
  
  extern bool assign_max_stack_depth(int newval, bool doit, GucSource source);
  #endif   /* BOOTSTRAP_INCLUDE */
--- 50,58 ----
  extern List *pg_parse_query(const char *query_string);
  extern List *pg_analyze_and_rewrite(Node *parsetree, const char *query_string,
  					   Oid *paramTypes, int numParams);
! extern Plan *pg_plan_query(Query *querytree, ParamListInfo boundParams, bool isCursor, int options);
  extern List *pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! 				bool needSnapshot, bool isCursor, int options);
  
  extern bool assign_max_stack_depth(int newval, bool doit, GucSource source);
  #endif   /* BOOTSTRAP_INCLUDE */
*** ./src/pl/plpgsql/src/gram.y.orig	2007-01-26 12:03:05.000000000 +0100
--- ./src/pl/plpgsql/src/gram.y	2007-01-26 21:40:57.000000000 +0100
***************
*** 29,35 ****
  static	PLpgSQL_expr	*read_sql_stmt(const char *sqlstart);
  static	PLpgSQL_type	*read_datatype(int tok);
  static	PLpgSQL_stmt	*make_execsql_stmt(const char *sqlstart, int lineno);
! static	PLpgSQL_stmt	*make_fetch_stmt(int lineno, int curvar);
  static	PLpgSQL_stmt	*make_return_stmt(int lineno);
  static	PLpgSQL_stmt	*make_return_next_stmt(int lineno);
  static	void			 check_assignable(PLpgSQL_datum *datum);
--- 29,35 ----
  static	PLpgSQL_expr	*read_sql_stmt(const char *sqlstart);
  static	PLpgSQL_type	*read_datatype(int tok);
  static	PLpgSQL_stmt	*make_execsql_stmt(const char *sqlstart, int lineno);
! static	PLpgSQL_stmt_fetch	*make_fetch_stmt(void);
  static	PLpgSQL_stmt	*make_return_stmt(int lineno);
  static	PLpgSQL_stmt	*make_return_next_stmt(int lineno);
  static	void			 check_assignable(PLpgSQL_datum *datum);
***************
*** 77,82 ****
--- 77,87 ----
  			char *end_label;
  			List *stmts;
  		}						loop_body;
+ 		struct 
+ 		{
+ 			int dno;
+ 			bool scrollable;
+ 		}						cursor_var;
  		List					*list;
  		PLpgSQL_type			*dtype;
  		PLpgSQL_datum			*scalar;	/* a VAR, RECFIELD, or TRIGARG */
***************
*** 92,97 ****
--- 97,103 ----
  		PLpgSQL_exception_block	*exception_block;
  		PLpgSQL_nsitem			*nsitem;
  		PLpgSQL_diag_item		*diagitem;
+ 		PLpgSQL_stmt_fetch		*fetch;
  }
  
  %type <declhdr> decl_sect
***************
*** 109,115 ****
  %type <expr>	expr_until_then expr_until_loop
  %type <expr>	opt_exitcond
  
! %type <ival>	assign_var cursor_variable
  %type <var>		cursor_varptr
  %type <variable>	decl_cursor_arg
  %type <forvariable>	for_variable
--- 115,122 ----
  %type <expr>	expr_until_then expr_until_loop
  %type <expr>	opt_exitcond
  
! %type <ival>	assign_var 
! %type <cursor_var>	cursor_variable
  %type <var>		cursor_varptr
  %type <variable>	decl_cursor_arg
  %type <forvariable>	for_variable
***************
*** 139,144 ****
--- 146,154 ----
  %type <diagitem> getdiag_list_item
  %type <ival>	getdiag_kind getdiag_target
  
+ %type <boolean>	opt_scrollable
+ %type <fetch>   opt_fetch_direction
+ 
  %type <ival>	lno
  
  		/*
***************
*** 176,181 ****
--- 186,192 ----
  %token	K_LOG
  %token	K_LOOP
  %token	K_NEXT
+ %token	K_NOSCROLL
  %token	K_NOT
  %token	K_NOTICE
  %token	K_NULL
***************
*** 188,193 ****
--- 199,205 ----
  %token	K_RESULT_OID
  %token	K_RETURN
  %token	K_REVERSE
+ %token	K_SCROLL
  %token	K_STRICT
  %token	K_THEN
  %token	K_TO
***************
*** 356,362 ****
  					{
  						plpgsql_ns_rename($2, $4);
  					}
! 				| decl_varname K_CURSOR
  					{ plpgsql_ns_push(NULL); }
  				  decl_cursor_args decl_is_from decl_cursor_query
  					{
--- 368,374 ----
  					{
  						plpgsql_ns_rename($2, $4);
  					}
! 				| decl_varname opt_scrollable K_CURSOR
  					{ plpgsql_ns_push(NULL); }
  				  decl_cursor_args decl_is_from decl_cursor_query
  					{
***************
*** 377,382 ****
--- 389,396 ----
  
  						curname_def = palloc0(sizeof(PLpgSQL_expr));
  
+ 						new->options = $2 ? CURSOR_OPT_SCROLL : 0;
+ 						
  						curname_def->dtype = PLPGSQL_DTYPE_EXPR;
  						strcpy(buf, "SELECT ");
  						cp1 = new->refname;
***************
*** 398,411 ****
  						curname_def->query = pstrdup(buf);
  						new->default_val = curname_def;
  
! 						new->cursor_explicit_expr = $6;
! 						if ($4 == NULL)
  							new->cursor_explicit_argrow = -1;
  						else
! 							new->cursor_explicit_argrow = $4->rowno;
  					}
  				;
  
  decl_cursor_query :
  					{
  						PLpgSQL_expr *query;
--- 412,440 ----
  						curname_def->query = pstrdup(buf);
  						new->default_val = curname_def;
  
! 						new->cursor_explicit_expr = $7;
! 						if ($5 == NULL)
  							new->cursor_explicit_argrow = -1;
  						else
! 							new->cursor_explicit_argrow = $5->rowno;
  					}
  				;
  
+ opt_scrollable :	
+ 					{
+ 						$$ = false;
+ 					}
+ 				| K_NOSCROLL
+ 					{
+ 						$$ = false;
+ 					}	
+ 				| K_SCROLL
+ 					{
+ 						$$ = true;
+ 					}	
+ 				;
+ 
+ 
  decl_cursor_query :
  					{
  						PLpgSQL_expr *query;
***************
*** 1429,1437 ****
  					}
  				;
  
! stmt_fetch		: K_FETCH lno cursor_variable K_INTO
  					{
! 						$$ = make_fetch_stmt($2, $3);
  					}
  				;
  
--- 1458,1498 ----
  					}
  				;
  
! stmt_fetch		: K_FETCH lno opt_fetch_direction cursor_variable K_INTO
  					{
! 						PLpgSQL_rec	   *rec;
! 						PLpgSQL_row	   *row;
! 						PLpgSQL_stmt_fetch	*fetch;
! 						
! 						fetch = $3;
! 
! 						/* We have already parsed everything through the INTO keyword */
! 						read_into_target(&rec, &row, NULL);
! 
! 	
! 						if (yylex() != ';')
! 							yyerror("syntax error");
! 
! 						fetch->lineno = $2;
! 						fetch->rec		= rec;
! 						fetch->row		= row;
! 						fetch->curvar		= $4.dno;
! 						
! 						if (!fetch->expr)
! 							if ((fetch->direction != FETCH_FORWARD || fetch->how_many < 0) && !$4.scrollable)
! 								ereport(ERROR,
!     								    	    (errcode(ERRCODE_INVALID_CURSOR_STATE),
! 									     errmsg("cursor can scan only forward"),
! 									     errhint("Declare cursor with SCROLL option to enable scrollable scan.")));
! 						
! 						$$ = (PLpgSQL_stmt *)fetch;
! 					}
! 				;
! 
! 
! opt_fetch_direction	: 
! 					{
! 						$$ = make_fetch_stmt();
  					}
  				;
  
***************
*** 1442,1448 ****
  						new = palloc(sizeof(PLpgSQL_stmt_close));
  						new->cmd_type = PLPGSQL_STMT_CLOSE;
  						new->lineno = $2;
! 						new->curvar = $3;
  
  						$$ = (PLpgSQL_stmt *)new;
  					}
--- 1503,1509 ----
  						new = palloc(sizeof(PLpgSQL_stmt_close));
  						new->cmd_type = PLPGSQL_STMT_CLOSE;
  						new->lineno = $2;
! 						new->curvar = $3.dno;
  
  						$$ = (PLpgSQL_stmt *)new;
  					}
***************
*** 1485,1491 ****
  									 errmsg("\"%s\" must be of type refcursor",
  											((PLpgSQL_var *) yylval.scalar)->refname)));
  						}
! 						$$ = yylval.scalar->dno;
  					}
  				;
  
--- 1546,1554 ----
  									 errmsg("\"%s\" must be of type refcursor",
  											((PLpgSQL_var *) yylval.scalar)->refname)));
  						}
! 
! 						$$.dno = yylval.scalar->dno;
! 						$$.scrollable = ((PLpgSQL_var *) yylval.scalar)->options & CURSOR_OPT_SCROLL;						
  					}
  				;
  
***************
*** 1972,1999 ****
  }
  
  
! static PLpgSQL_stmt *
! make_fetch_stmt(int lineno, int curvar)
  {
  	int					tok;
! 	PLpgSQL_rec		   *rec;
! 	PLpgSQL_row		   *row;
  	PLpgSQL_stmt_fetch *fetch;
  
- 	/* We have already parsed everything through the INTO keyword */
- 	read_into_target(&rec, &row, NULL);
- 	tok = yylex();
- 	if (tok != ';')
- 		yyerror("syntax error");
- 
  	fetch = palloc0(sizeof(PLpgSQL_stmt_fetch));
  	fetch->cmd_type = PLPGSQL_STMT_FETCH;
- 	fetch->lineno	= lineno;
- 	fetch->rec		= rec;
- 	fetch->row		= row;
- 	fetch->curvar	= curvar;
  
! 	return (PLpgSQL_stmt *) fetch;
  }
  
  
--- 2035,2109 ----
  }
  
  
! static PLpgSQL_stmt_fetch *
! make_fetch_stmt()
  {
  	int					tok;
! 	char	*token = NULL;
! 	bool	check_FROM = true;
  	PLpgSQL_stmt_fetch *fetch;
  
  	fetch = palloc0(sizeof(PLpgSQL_stmt_fetch));
  	fetch->cmd_type = PLPGSQL_STMT_FETCH;
  
! 	tok = yylex();
! 
! 	if (tok != K_NEXT && tok != K_FROM)
! 	    plpgsql_convert_ident(yytext, &token, 1);
! 
! 	fetch->direction = FETCH_FORWARD;
! 	fetch->how_many  = 1;
! 
! 	/* 
!          * Tokenized words have to be tested first,
!          * because token is NULL
!          */
! 	if (tok == K_FROM)
! 	{
! 		/* read optional FROM */
! 		check_FROM = false;
! 	}
! 	else if (tok == K_NEXT)
! 	{
! 		/* use default */
! 	}
! 	else if (strcmp(token, "prior") == 0)
! 	{
! 		fetch->direction = FETCH_BACKWARD;
! 	}
! 	else if (strcmp(token, "first") == 0)
! 	{
! 		fetch->direction = FETCH_ABSOLUTE;
! 	}
! 	else if (strcmp(token, "last") == 0)
! 	{
! 		fetch->direction = FETCH_ABSOLUTE;
! 		fetch->how_many  = -1;
! 	}
! 	else if (strcmp(token, "absolute") == 0)
! 	{
! 		fetch->direction = FETCH_ABSOLUTE;
! 		fetch->expr = plpgsql_read_expression(K_FROM, "FROM");
! 		check_FROM  = false;
! 	}
! 	else if (strcmp(token, "relative") == 0)
! 	{
! 		fetch->direction = FETCH_RELATIVE;
! 		fetch->expr = plpgsql_read_expression(K_FROM, "FROM");
! 		check_FROM  = false;		
! 	}
! 	else
! 	{
! 		/* propably cursor_variable */
! 		check_FROM = false;
! 		plpgsql_push_back_token(tok);
! 	}
! 
! 	/* check FROM keyword after direction's specification */
! 	if (check_FROM && yylex() != K_FROM)
! 		yyerror("expected \"FROM\"");
! 
! 	return fetch;
  }
  
  
*** ./src/pl/plpgsql/src/pl_exec.c.orig	2007-01-26 14:21:25.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c	2007-01-26 20:19:51.000000000 +0100
***************
*** 102,108 ****
  static void exec_eval_cleanup(PLpgSQL_execstate *estate);
  
  static void exec_prepare_plan(PLpgSQL_execstate *estate,
! 				  PLpgSQL_expr *expr);
  static bool exec_simple_check_node(Node *node);
  static void exec_simple_check_plan(PLpgSQL_expr *expr);
  static Datum exec_eval_simple_expr(PLpgSQL_execstate *estate,
--- 102,108 ----
  static void exec_eval_cleanup(PLpgSQL_execstate *estate);
  
  static void exec_prepare_plan(PLpgSQL_execstate *estate,
! 				  PLpgSQL_expr *expr, int options);
  static bool exec_simple_check_node(Node *node);
  static void exec_simple_check_plan(PLpgSQL_expr *expr);
  static Datum exec_eval_simple_expr(PLpgSQL_execstate *estate,
***************
*** 2201,2207 ****
   */
  static void
  exec_prepare_plan(PLpgSQL_execstate *estate,
! 				  PLpgSQL_expr *expr)
  {
  	int			i;
  	_SPI_plan  *spi_plan;
--- 2201,2207 ----
   */
  static void
  exec_prepare_plan(PLpgSQL_execstate *estate,
! 				  PLpgSQL_expr *expr, int options)
  {
  	int			i;
  	_SPI_plan  *spi_plan;
***************
*** 2225,2233 ****
  	}
  
  	/*
! 	 * Generate and save the plan
  	 */
! 	plan = SPI_prepare(expr->query, expr->nparams, argtypes);
  	if (plan == NULL)
  	{
  		/* Some SPI errors deserve specific error messages */
--- 2225,2237 ----
  	}
  
  	/*
! 	 * Generate and save the plan, if any option is know, use it
  	 */
! 	if (options)
! 		plan = SPI_prepare_cursor(expr->query, expr->nparams, argtypes, options);
! 	else
! 		plan = SPI_prepare(expr->query, expr->nparams, argtypes);
! 
  	if (plan == NULL)
  	{
  		/* Some SPI errors deserve specific error messages */
***************
*** 2287,2293 ****
  		_SPI_plan  *spi_plan;
  		ListCell   *l;
  
! 		exec_prepare_plan(estate, expr);
  		stmt->mod_stmt = false;
  		spi_plan = (_SPI_plan *) expr->plan;
  		foreach(l, spi_plan->qtlist)
--- 2291,2297 ----
  		_SPI_plan  *spi_plan;
  		ListCell   *l;
  
! 		exec_prepare_plan(estate, expr, 0);
  		stmt->mod_stmt = false;
  		spi_plan = (_SPI_plan *) expr->plan;
  		foreach(l, spi_plan->qtlist)
***************
*** 2858,2864 ****
  		 */
  		query = stmt->query;
  		if (query->plan == NULL)
! 			exec_prepare_plan(estate, query);
  	}
  	else if (stmt->dynquery != NULL)
  	{
--- 2862,2868 ----
  		 */
  		query = stmt->query;
  		if (query->plan == NULL)
! 			exec_prepare_plan(estate, query, 0);
  	}
  	else if (stmt->dynquery != NULL)
  	{
***************
*** 2960,2967 ****
  		}
  
  		query = curvar->cursor_explicit_expr;
  		if (query->plan == NULL)
! 			exec_prepare_plan(estate, query);
  	}
  
  	/* ----------
--- 2964,2972 ----
  		}
  
  		query = curvar->cursor_explicit_expr;
+     		    
  		if (query->plan == NULL)
! 			exec_prepare_plan(estate, query, curvar->options);
  	}
  
  	/* ----------
***************
*** 3029,3034 ****
--- 3034,3040 ----
  	Portal		portal;
  	char	   *curname;
  	int			n;
+ 	int			how_many = stmt->how_many;
  
  	/* ----------
  	 * Get the portal of the cursor by name
***************
*** 3048,3053 ****
--- 3054,3082 ----
  				 errmsg("cursor \"%s\" does not exist", curname)));
  	pfree(curname);
  
+ 	/* calculate position for FETCH_RELATIVE or FETCH_ABSOLUTE */
+ 	if (stmt->expr)
+ 	{
+ 		bool isnull;
+ 	    
+ 		how_many = exec_eval_integer(estate, stmt->expr, &isnull);
+ 
+ 		if (isnull)
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ 					 errmsg("relative or absolute cursor position is NULL")));
+ 
+ 		exec_eval_cleanup(estate);
+ 	}
+ 
+ 	/* check if we neeed scrollable corsor */
+ 	if (!(curvar->options & CURSOR_OPT_SCROLL))
+ 		if (stmt->direction != FETCH_FORWARD && how_many < 0)
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_INVALID_CURSOR_STATE),
+ 					 errmsg("cursor can scan only forward"),
+ 					 errhint("Declare cursor with SCROLL option to enable scrollable scan.")));
+ 
  	/* ----------
  	 * Determine if we fetch into a record or a row
  	 * ----------
***************
*** 3063,3069 ****
  	 * Fetch 1 tuple from the cursor
  	 * ----------
  	 */
! 	SPI_cursor_fetch(portal, true, 1);
  	tuptab = SPI_tuptable;
  	n = SPI_processed;
  
--- 3092,3098 ----
  	 * Fetch 1 tuple from the cursor
  	 * ----------
  	 */
! 	SPI_scroll_cursor_fetch(portal, stmt->direction, how_many);
  	tuptab = SPI_tuptable;
  	n = SPI_processed;
  
***************
*** 3776,3782 ****
  	 * If not already done create a plan for this expression
  	 */
  	if (expr->plan == NULL)
! 		exec_prepare_plan(estate, expr);
  
  	/*
  	 * If this is a simple expression, bypass SPI and use the executor
--- 3805,3811 ----
  	 * If not already done create a plan for this expression
  	 */
  	if (expr->plan == NULL)
! 		exec_prepare_plan(estate, expr, 0);
  
  	/*
  	 * If this is a simple expression, bypass SPI and use the executor
***************
*** 3840,3846 ****
  	 * On the first call for this expression generate the plan
  	 */
  	if (expr->plan == NULL)
! 		exec_prepare_plan(estate, expr);
  
  	/*
  	 * Now build up the values and nulls arguments for SPI_execute_plan()
--- 3869,3875 ----
  	 * On the first call for this expression generate the plan
  	 */
  	if (expr->plan == NULL)
! 		exec_prepare_plan(estate, expr, 0);
  
  	/*
  	 * Now build up the values and nulls arguments for SPI_execute_plan()
*** ./src/pl/plpgsql/src/plpgsql.h.orig	2007-01-26 12:14:07.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h	2007-01-26 18:48:18.000000000 +0100
***************
*** 204,209 ****
--- 204,210 ----
  	PLpgSQL_expr *default_val;
  	PLpgSQL_expr *cursor_explicit_expr;
  	int			cursor_explicit_argrow;
+ 	int		options;
  
  	Datum		value;
  	bool		isnull;
***************
*** 447,452 ****
--- 448,456 ----
  	PLpgSQL_rec *rec;
  	PLpgSQL_row *row;
  	int			curvar;
+ 	int			direction;
+ 	int			how_many;
+ 	PLpgSQL_expr *expr;
  } PLpgSQL_stmt_fetch;
  
  
*** ./src/pl/plpgsql/src/scan.l.orig	2007-01-26 12:02:48.000000000 +0100
--- ./src/pl/plpgsql/src/scan.l	2007-01-26 14:14:54.000000000 +0100
***************
*** 155,160 ****
--- 155,161 ----
  return			{ return K_RETURN;			}
  reverse			{ return K_REVERSE;			}
  row_count		{ return K_ROW_COUNT;		}
+ scroll			{ return K_SCROLL;		}
  strict			{ return K_STRICT;	    	}
  then			{ return K_THEN;			}
  to				{ return K_TO;				}
***************
*** 174,179 ****
--- 175,182 ----
  	 * in the pl_comp.c subroutines will point to the right place.
       * ----------
       */
+ NO{space}*SCROLL				{
+ 	return K_NOSCROLL; }
  {identifier}					{
  	plpgsql_error_lineno = plpgsql_scanner_lineno();
  	return plpgsql_parse_word(yytext); }
*** ./src/test/regress/expected/plpgsql.out.orig	2007-01-26 20:25:10.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out	2007-01-26 20:23:16.000000000 +0100
***************
*** 2934,2936 ****
--- 2934,2995 ----
  ERROR:  query returned more than one row
  CONTEXT:  PL/pgSQL function "footest" line 4 at execute statement
  drop function footest();
+ -- scrollable cursor support
+ --shoud fail, early detect scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare 
+   x integer;
+   y integer;
+   c no scroll cursor(w int) for 
+           select a.a, b.b 
+             from generate_series(1,2) a(a), generate_series(1,10) b(b)
+            where a = w;
+ begin
+   open c($1);
+   fetch last from c into x, y;
+   while found loop
+     raise notice '% %', x, y;
+     fetch prior from c into x, y;
+   end loop;
+   close c;
+ end;
+ $$ language plpgsql;
+ ERROR:  cursor can scan only forward
+ HINT:  Declare cursor with SCROLL option to enable scrollable scan.
+ CONTEXT:  compile of PL/pgSQL function "sc_test" near line 10
+ --shoud work, early detect used scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare 
+   x integer;
+   y integer;
+   c scroll cursor(w int) for 
+           select a.a, b.b 
+             from generate_series(1,2) a(a), generate_series(1,10) b(b)
+            where a = w;
+ begin
+   open c($1);
+   fetch last from c into x, y;
+   while found loop
+     raise notice '% %', x, y;
+     fetch prior from c into x, y;
+   end loop;
+   close c;
+ end;
+ $$ language plpgsql;
+ select sc_test(1);
+ NOTICE:  1 10
+ NOTICE:  1 9
+ NOTICE:  1 8
+ NOTICE:  1 7
+ NOTICE:  1 6
+ NOTICE:  1 5
+ NOTICE:  1 4
+ NOTICE:  1 3
+ NOTICE:  1 2
+ NOTICE:  1 1
+  sc_test 
+ ---------
+  
+ (1 row)
+ 
+ drop function sc_test(int);
*** ./src/test/regress/sql/plpgsql.sql.orig	2007-01-26 16:00:24.000000000 +0100
--- ./src/test/regress/sql/plpgsql.sql	2007-01-26 20:19:55.000000000 +0100
***************
*** 2440,2442 ****
--- 2440,2488 ----
  select footest();
  
  drop function footest();
+ 
+ -- scrollable cursor support
+ 
+ --shoud fail, early detect scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare 
+   x integer;
+   y integer;
+   c no scroll cursor(w int) for 
+           select a.a, b.b 
+             from generate_series(1,2) a(a), generate_series(1,10) b(b)
+            where a = w;
+ begin
+   open c($1);
+   fetch last from c into x, y;
+   while found loop
+     raise notice '% %', x, y;
+     fetch prior from c into x, y;
+   end loop;
+   close c;
+ end;
+ $$ language plpgsql;
+ 
+ --shoud work, early detect used scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare 
+   x integer;
+   y integer;
+   c scroll cursor(w int) for 
+           select a.a, b.b 
+             from generate_series(1,2) a(a), generate_series(1,10) b(b)
+            where a = w;
+ begin
+   open c($1);
+   fetch last from c into x, y;
+   while found loop
+     raise notice '% %', x, y;
+     fetch prior from c into x, y;
+   end loop;
+   close c;
+ end;
+ $$ language plpgsql;
+ 
+ select sc_test(1);
+ 
+ drop function sc_test(int);

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to