Hello

I refreshed Magnus's patch http://archives.postgresql.org/pgsql-patches/2007-02/msg00275.php from februar.


Regards

Pavel Stehule

p.s. scrollable cursors in plpgsql need little work still. I forgot for nonstandard (postgresql extension) direction forward all, forward n, backward n. Forward all propably hasn't sense.

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig	2007-04-20 09:01:50.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml	2007-04-20 09:09:04.000000000 +0200
***************
*** 1524,1529 ****
--- 1524,1536 ----
            </listitem>
            <listitem>
             <para>
+                 A <command>MOVE</> statement sets <literal>FOUND</literal>
+                 true if is success, false if is out of table.
+            </para>
+           </listitem>
+ 
+           <listitem>
+            <para>
                  A <command>FOR</> statement sets <literal>FOUND</literal> true
                  if it iterates one or more times, else false.  This applies to
                  all three variants of the <command>FOR</> statement (integer
***************
*** 2567,2572 ****
--- 2574,2624 ----
       </sect3>
  
      <sect3>
+      <title><literal>MOVE</></title>
+ 
+ <synopsis>
+ MOVE <optional> <replaceable>direction</replaceable> FROM </optional> <replaceable>cursor</replaceable>;
+ </synopsis>
+ 
+     <para>
+     MOVE repositions a cursor without retrieving any data. MOVE works 
+     exactly like the FETCH  command, except it only positions the 
+     cursor and does not return rows. As with <command>SELECT
+      INTO</command>, the special variable <literal>FOUND</literal> can
+      be checked to see whether a cursor was repositioned or not.
+     </para>
+ 
+     <para>
+      The <replaceable>direction</replaceable> clause can be any of the
+      variants allowed in the SQL <xref linkend="sql-fetch"
+      endterm="sql-fetch-title"> command except the ones that can fetch
+      more than one row; namely, it can be
+      <literal>NEXT</>,
+      <literal>PRIOR</>,
+      <literal>FIRST</>,
+      <literal>LAST</>,
+      <literal>ABSOLUTE</> <replaceable>count</replaceable>,
+      <literal>RELATIVE</> <replaceable>count</replaceable>,
+      <literal>FORWARD</>, or
+      <literal>BACKWARD</>.
+      Omitting <replaceable>direction</replaceable> is the same
+      as specifying <literal>NEXT</>.
+      <replaceable>direction</replaceable> values that require moving
+      backward are likely to fail unless the cursor was declared or opened
+      with the <literal>SCROLL</> option.
+     </para>
+ 
+     <para>
+      Examples:
+ <programlisting>
+ MOVE curs1;
+ MOVE LAST FROM curs3;
+ MOVE RELATIVE -2 FROM curs4;
+ </programlisting>
+        </para>
+      </sect3>
+ 
+     <sect3>
       <title><literal>CLOSE</></title>
  
  <synopsis>
*** ./src/pl/plpgsql/src/gram.y.orig	2007-04-19 19:15:28.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y	2007-04-19 19:39:36.000000000 +0200
***************
*** 125,131 ****
  %type <stmt>	stmt_assign stmt_if stmt_loop stmt_while stmt_exit
  %type <stmt>	stmt_return stmt_raise stmt_execsql stmt_execsql_insert
  %type <stmt>	stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type <stmt>	stmt_open stmt_fetch stmt_close stmt_null
  
  %type <list>	proc_exceptions
  %type <exception_block> exception_sect
--- 125,131 ----
  %type <stmt>	stmt_assign stmt_if stmt_loop stmt_while stmt_exit
  %type <stmt>	stmt_return stmt_raise stmt_execsql stmt_execsql_insert
  %type <stmt>	stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type <stmt>	stmt_open stmt_fetch stmt_move stmt_close stmt_null
  
  %type <list>	proc_exceptions
  %type <exception_block> exception_sect
***************
*** 179,184 ****
--- 179,185 ----
  %token	K_IS
  %token	K_LOG
  %token	K_LOOP
+ %token	K_MOVE
  %token	K_NEXT
  %token	K_NOSCROLL
  %token	K_NOT
***************
*** 635,640 ****
--- 636,643 ----
  						{ $$ = $1; }
  				| stmt_fetch
  						{ $$ = $1; }
+ 				| stmt_move
+ 						{ $$ = $1; }
  				| stmt_close
  						{ $$ = $1; }
  				| stmt_null
***************
*** 1478,1483 ****
--- 1481,1499 ----
  						fetch->rec		= rec;
  						fetch->row		= row;
  						fetch->curvar	= $4->varno;
+ 						fetch->is_move	= false;
+ 
+ 						$$ = (PLpgSQL_stmt *)fetch;
+ 					}
+ 				;
+ 				
+ stmt_move		: K_MOVE lno opt_fetch_direction cursor_variable ';'
+ 					{
+ 						PLpgSQL_stmt_fetch *fetch = $3;
+ 
+ 						fetch->lineno = $2;
+ 						fetch->curvar	= $4->varno;
+ 						fetch->is_move	= true;
  
  						$$ = (PLpgSQL_stmt *)fetch;
  					}
*** ./src/pl/plpgsql/src/pl_exec.c.orig	2007-04-20 09:24:27.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c	2007-04-20 09:25:14.000000000 +0200
***************
*** 3112,3118 ****
  	return PLPGSQL_RC_OK;
  }
  
- 
  /* ----------
   * exec_stmt_fetch			Fetch from a cursor into a target
   * ----------
--- 3112,3117 ----
***************
*** 3164,3208 ****
  	}
  
  	/* ----------
! 	 * Determine if we fetch into a record or a row
! 	 * ----------
! 	 */
! 	if (stmt->rec != NULL)
! 		rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]);
! 	else if (stmt->row != NULL)
! 		row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]);
! 	else
! 		elog(ERROR, "unsupported target");
! 
! 	/* ----------
! 	 * Fetch 1 tuple from the cursor
  	 * ----------
  	 */
! 	SPI_scroll_cursor_fetch(portal, stmt->direction, how_many);
! 	tuptab = SPI_tuptable;
! 	n = SPI_processed;
! 
! 	/* ----------
! 	 * Set the target and the global FOUND variable appropriately.
! 	 * ----------
! 	 */
! 	if (n == 0)
  	{
! 		exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
! 		exec_set_found(estate, false);
  	}
  	else
  	{
! 		exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
! 		exec_set_found(estate, true);
  	}
  
- 	SPI_freetuptable(tuptab);
- 
  	return PLPGSQL_RC_OK;
  }
  
- 
  /* ----------
   * exec_stmt_close			Close a cursor
   * ----------
--- 3163,3224 ----
  	}
  
  	/* ----------
! 	 * Fetch 1 tuple from the cursor or move
  	 * ----------
  	 */
! 	if (!stmt->is_move)
  	{
! 		/* ----------
! 		 * Determine if we fetch into a record or a row
! 		 * ----------
! 		 */
! 		if (stmt->rec != NULL)
! 			rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]);
! 		else if (stmt->row != NULL)
! 			row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]);
! 		else
! 			elog(ERROR, "unsupported target");
! 
! 		SPI_scroll_cursor_fetch(portal, stmt->direction, how_many);
! 		tuptab = SPI_tuptable;
! 		n = SPI_processed;
! 
! 		/* ----------
! 		 * Set the target and the global FOUND variable appropriately.
! 		 * ----------
! 		 */
! 		if (n == 0)
! 		{
! 			exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
! 			exec_set_found(estate, false);
! 		}
! 		else
!     		{
! 			exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
! 			exec_set_found(estate, true);
! 		}
! 		
! 		SPI_freetuptable(tuptab);
  	}
  	else
  	{
! 		/* ----------
! 		 * Move a cursor
! 		 * ----------
! 		 */
! 		SPI_scroll_cursor_move(portal, stmt->direction, how_many);
! 		n = SPI_processed;
! 		
! 		/* ----------
! 		 * Set global FOUND variable appropriately.
! 		 * ----------
! 		 */
! 		exec_set_found(estate, n != 0);
  	}
  
  	return PLPGSQL_RC_OK;
  }
  
  /* ----------
   * exec_stmt_close			Close a cursor
   * ----------
*** ./src/pl/plpgsql/src/pl_funcs.c.orig	2007-04-20 08:46:12.000000000 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c	2007-04-20 08:43:01.000000000 +0200
***************
*** 496,501 ****
--- 496,502 ----
  static void dump_close(PLpgSQL_stmt_close *stmt);
  static void dump_perform(PLpgSQL_stmt_perform *stmt);
  static void dump_expr(PLpgSQL_expr *expr);
+ static void dump_cursor_direction(PLpgSQL_stmt_fetch *stmt);
  
  
  static void
***************
*** 761,781 ****
  dump_fetch(PLpgSQL_stmt_fetch *stmt)
  {
  	dump_ind();
! 	printf("FETCH curvar=%d\n", stmt->curvar);
! 
! 	dump_indent += 2;
! 	if (stmt->rec != NULL)
  	{
! 		dump_ind();
! 		printf("    target = %d %s\n", stmt->rec->recno, stmt->rec->refname);
  	}
! 	if (stmt->row != NULL)
  	{
! 		dump_ind();
! 		printf("    target = %d %s\n", stmt->row->rowno, stmt->row->refname);
  	}
- 	dump_indent -= 2;
- 
  }
  
  static void
--- 762,791 ----
  dump_fetch(PLpgSQL_stmt_fetch *stmt)
  {
  	dump_ind();
! 	
! 	if (!stmt->is_move)
  	{
! 		printf("FETCH curvar=%d\n", stmt->curvar);
! 		dump_cursor_direction(stmt);
! 
! 		dump_indent += 2;
! 		if (stmt->rec != NULL)
! 		{
! 			dump_ind();
! 			printf("    target = %d %s\n", stmt->rec->recno, stmt->rec->refname);
! 		}
! 		if (stmt->row != NULL)
! 		{
! 			dump_ind();
! 			printf("    target = %d %s\n", stmt->row->rowno, stmt->row->refname);
! 		}
! 		dump_indent -= 2;
  	}
! 	else
  	{
! 		printf("MOVE curvar=%d\n", stmt->curvar);
! 		dump_cursor_direction(stmt);
  	}
  }
  
  static void
***************
*** 1067,1069 ****
--- 1077,1113 ----
  	printf("\nEnd of execution tree of function %s\n\n", func->fn_name);
  	fflush(stdout);
  }
+ 
+ static void
+ dump_cursor_direction(PLpgSQL_stmt_fetch *stmt)
+ {
+ 	dump_indent += 2;
+ 	dump_ind();
+ 	switch (stmt->direction)
+ 	{
+ 		case FETCH_FORWARD:
+ 			printf("    FORWARD ");
+ 			break;
+ 		case FETCH_BACKWARD:
+ 			printf("    BACKWARD ");
+ 			break;
+ 		case FETCH_ABSOLUTE:
+ 			printf("    ABSOLUTE ");
+ 			break;
+ 		case FETCH_RELATIVE:
+ 			printf("    RELATIVE ");
+ 			break;
+ 		default:
+ 			printf("??? unknown cursor direction %d\n", stmt->direction);
+ 	}
+ 	
+ 	if (stmt->expr)
+ 	{
+ 		dump_expr(stmt->expr);
+ 		printf("\n");
+ 	}
+ 	else
+ 		printf("%d\n", stmt->how_many);
+ 		
+ 	dump_indent -= 2;
+ }
*** ./src/pl/plpgsql/src/plpgsql.h.orig	2007-04-19 19:19:55.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h	2007-04-19 19:21:39.000000000 +0200
***************
*** 446,452 ****
  
  
  typedef struct
! {								/* FETCH statement */
  	int			cmd_type;
  	int			lineno;
  	PLpgSQL_rec *rec;			/* target, as record or row */
--- 446,452 ----
  
  
  typedef struct
! {								/* FETCH or MOVE statement */
  	int			cmd_type;
  	int			lineno;
  	PLpgSQL_rec *rec;			/* target, as record or row */
***************
*** 455,460 ****
--- 455,461 ----
  	FetchDirection direction;	/* fetch direction */
  	int			how_many;		/* count, if constant (expr is NULL) */
  	PLpgSQL_expr *expr;			/* count, if expression */
+ 	bool		is_move;		/* Is this an fetch or move? */
  } PLpgSQL_stmt_fetch;
  
  
*** ./src/pl/plpgsql/src/scan.l.orig	2007-04-19 19:15:34.000000000 +0200
--- ./src/pl/plpgsql/src/scan.l	2007-04-19 19:16:19.000000000 +0200
***************
*** 142,147 ****
--- 142,148 ----
  is				{ return K_IS;				}
  log				{ return K_LOG;				}
  loop			{ return K_LOOP;			}
+ move			{ return K_MOVE;			}
  next			{ return K_NEXT;			}
  no{space}+scroll { return K_NOSCROLL;		}
  not				{ return K_NOT;				}
*** ./src/test/regress/expected/plpgsql.out.orig	2007-04-20 08:57:51.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out	2007-04-20 08:56:57.000000000 +0200
***************
*** 3024,3026 ****
--- 3024,3051 ----
  (3 rows)
  
  drop function sc_test();
+ create or replace function sc_test() returns setof integer as $$
+ declare
+   c cursor for select * from generate_series(1,10);
+   x integer;
+ begin
+   open c;
+   fetch c into x;
+   while found loop
+     return next x;
+     move relative 2 from c;
+     fetch c into x;
+   end loop;
+   close c;
+ end;
+ $$ language plpgsql;
+ select * from sc_test();
+  sc_test 
+ ---------
+        1
+        4
+        7
+       10
+ (4 rows)
+ 
+ drop function sc_test();
*** ./src/test/regress/sql/plpgsql.sql.orig	2007-04-20 08:50:20.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql	2007-04-20 08:55:43.000000000 +0200
***************
*** 2512,2514 ****
--- 2512,2535 ----
  select * from sc_test();
  
  drop function sc_test();
+ 
+ create or replace function sc_test() returns setof integer as $$
+ declare
+   c cursor for select * from generate_series(1,10);
+   x integer;
+ begin
+   open c;
+   fetch c into x;
+   while found loop
+     return next x;
+     move relative 2 from c;
+     fetch c into x;
+   end loop;
+   close c;
+ end;
+ $$ language plpgsql;
+ 
+ select * from sc_test();
+ 
+ drop function sc_test();
+ 

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

Reply via email to