Support for MOVE in PL/PgSQL. Initial patch from Magnus, some improvements

by Pavel Stehule, and reviewed by Neil Conway.

7 files changed, 171 insertions, 46 deletions

diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 48c2ed67852..164a2179a31 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.101 2007/04/28 23:54:59 neilc Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.102 2007/04/29 01:21:09 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -125,7 +125,7 @@ static	void			 check_labels(const char *start_label,
 %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 <stmt>	stmt_open stmt_fetch stmt_move stmt_close stmt_null
 
 %type <list>	proc_exceptions
 %type <exception_block> exception_sect
@@ -179,6 +179,7 @@ static	void			 check_labels(const char *start_label,
 %token	K_IS
 %token	K_LOG
 %token	K_LOOP
+%token	K_MOVE
 %token	K_NEXT
 %token	K_NOSCROLL
 %token	K_NOT
@@ -635,6 +636,8 @@ proc_stmt		: pl_block ';'
 						{ $$ = $1; }
 				| stmt_fetch
 						{ $$ = $1; }
+				| stmt_move
+						{ $$ = $1; }
 				| stmt_close
 						{ $$ = $1; }
 				| stmt_null
@@ -1478,6 +1481,19 @@ stmt_fetch		: K_FETCH lno opt_fetch_direction cursor_variable K_INTO
 						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;
 					}
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 620b363bda8..af6430f1ff4 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.195 2007/04/19 16:33:24 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.196 2007/04/29 01:21:09 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -3114,7 +3114,8 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
 
 
 /* ----------
- * exec_stmt_fetch			Fetch from a cursor into a target
+ * exec_stmt_fetch			Fetch from a cursor into a target, or just
+ *                          move the current position of the cursor
  * ----------
  */
 static int
@@ -3163,46 +3164,57 @@ exec_stmt_fetch(PLpgSQL_execstate *estate, PLpgSQL_stmt_fetch *stmt)
 		exec_eval_cleanup(estate);
 	}
 
-	/* ----------
-	 * 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");
+	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");
 
-	/* ----------
-	 * Fetch 1 tuple from the cursor
-	 * ----------
-	 */
-	SPI_scroll_cursor_fetch(portal, stmt->direction, how_many);
-	tuptab = SPI_tuptable;
-	n = SPI_processed;
+		/* ----------
+		 * 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);
+		/* ----------
+		 * 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
 	{
-		exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
-		exec_set_found(estate, true);
-	}
+		/* Move the cursor */
+		SPI_scroll_cursor_move(portal, stmt->direction, how_many);
+		n = SPI_processed;
 
-	SPI_freetuptable(tuptab);
+		/* Set the global FOUND variable appropriately. */
+		exec_set_found(estate, n != 0);
+	}
 
 	return PLPGSQL_RC_OK;
 }
 
-
 /* ----------
  * exec_stmt_close			Close a cursor
  * ----------
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index da1b2d48fd8..c344c9e4ea7 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.58 2007/03/18 05:36:49 neilc Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.59 2007/04/29 01:21:09 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -493,6 +493,7 @@ static void dump_dynfors(PLpgSQL_stmt_dynfors *stmt);
 static void dump_getdiag(PLpgSQL_stmt_getdiag *stmt);
 static void dump_open(PLpgSQL_stmt_open *stmt);
 static void dump_fetch(PLpgSQL_stmt_fetch *stmt);
+static void dump_cursor_direction(PLpgSQL_stmt_fetch *stmt);
 static void dump_close(PLpgSQL_stmt_close *stmt);
 static void dump_perform(PLpgSQL_stmt_perform *stmt);
 static void dump_expr(PLpgSQL_expr *expr);
@@ -761,21 +762,64 @@ static void
 dump_fetch(PLpgSQL_stmt_fetch *stmt)
 {
 	dump_ind();
-	printf("FETCH curvar=%d\n", stmt->curvar);
+	
+	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
+dump_cursor_direction(PLpgSQL_stmt_fetch *stmt)
+{
 	dump_indent += 2;
-	if (stmt->rec != NULL)
+	dump_ind();
+	switch (stmt->direction)
 	{
-		dump_ind();
-		printf("    target = %d %s\n", stmt->rec->recno, stmt->rec->refname);
+		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", stmt->direction);
 	}
-	if (stmt->row != NULL)
+	
+	if (stmt->expr)
 	{
-		dump_ind();
-		printf("    target = %d %s\n", stmt->row->rowno, stmt->row->refname);
+		dump_expr(stmt->expr);
+		printf("\n");
 	}
+	else
+		printf("%d\n", stmt->how_many);
+		
 	dump_indent -= 2;
-
 }
 
 static void
@@ -1067,3 +1111,4 @@ plpgsql_dumptree(PLpgSQL_function *func)
 	printf("\nEnd of execution tree of function %s\n\n", func->fn_name);
 	fflush(stdout);
 }
+
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index b2c07113102..20ee074564a 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.87 2007/04/16 17:21:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.88 2007/04/29 01:21:09 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -446,7 +446,7 @@ typedef struct
 
 
 typedef struct
-{								/* FETCH statement */
+{								/* FETCH or MOVE statement */
 	int			cmd_type;
 	int			lineno;
 	PLpgSQL_rec *rec;			/* target, as record or row */
@@ -455,6 +455,7 @@ typedef struct
 	FetchDirection direction;	/* fetch direction */
 	int			how_many;		/* count, if constant (expr is NULL) */
 	PLpgSQL_expr *expr;			/* count, if expression */
+	bool		is_move;		/* is this a fetch or move? */
 } PLpgSQL_stmt_fetch;
 
 
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index df71faacd8c..0d71dd6436a 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.56 2007/04/16 17:21:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.57 2007/04/29 01:21:09 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -142,6 +142,7 @@ into			{ return K_INTO;			}
 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;				}
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 669077edee7..12a6d4e4e49 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3023,4 +3023,31 @@ select * from sc_test();
            0
 (3 rows)
 
+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;
+  loop
+      move relative 2 in c;
+      if not found then
+          exit;
+      end if;
+      fetch next from c into x;
+      if found then
+          return next x;
+      end if;
+  end loop;
+  close c;
+end;
+$$ language plpgsql;
+select * from sc_test();
+ sc_test 
+---------
+       3
+       6
+       9
+(3 rows)
+
 drop function sc_test();
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 33637d9e798..ee9de0a5838 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2511,4 +2511,27 @@ $$ language plpgsql;
 
 select * from 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;
+  loop
+      move relative 2 in c;
+      if not found then
+          exit;
+      end if;
+      fetch next from c into x;
+      if found then
+          return next x;
+      end if;
+  end loop;
+  close c;
+end;
+$$ language plpgsql;
+
+select * from sc_test();
+
 drop function sc_test();
+