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
