Support for cursors in PL/pgSQL

This patch adds support for cursors in PL/pgSQL. The syntax generally
follows Oracle's PL/SQL.

Cursors are not required in a language like PL/pgSQL, since the FOR
statement permits the same sorts of operations. But being able to
name cursors can be convenient; for example, it allows easily stepping
through two tables simultaneously. Cursor support also makes it
easier for people to port Oracle PL/SQL stored procedures to Postgres.

This patch includes documentation and a regression test.

This patch adds four new keywords to PL/pgSQL: CURSOR, OPEN, FETCH,
and CLOSE. CURSOR, FETCH, and CLOSE are keywords in SQL, and as such
are unlikely to be used in existing PL/pgSQL programs. However, it is
possible that adding OPEN as a keyword will break some existing code.

It is presumably too late to get this patch into the 7.1 release. I
would like to work with the maintainers to get this patch into 7.2 and
future releases.

This work is contributed by Zembu.

Ian

Index: doc/src/sgml/plsql.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/plsql.sgml,v
retrieving revision 2.18
diff -p -u -r2.18 plsql.sgml
--- doc/src/sgml/plsql.sgml 2001/01/20 20:59:29 2.18
+++ doc/src/sgml/plsql.sgml 2001/01/23 00:07:16
@@ -181,18 +181,21 @@ END;

<varlistentry>
<term>
-<replaceable>name</replaceable> <replaceable>table</replaceable>%ROWTYPE;
+<replaceable>name</replaceable> <replaceable>table</replaceable>%ROWTYPE | <replaceable>cursor</replaceable>%ROWTYPE;
</term>
<listitem>
<para>
- Declares a row with the structure of the given table. <replaceable>table</replaceable> must be
- an existing table or view name of the database. The fields of the row
- are accessed in the dot notation. Parameters to a function can
- be composite types (complete table rows). In that case, the
- corresponding identifier $n will be a rowtype, but it
- must be aliased using the ALIAS command described below. Only the user
- attributes of a table row are accessible in the row, no Oid or other
- system attributes (hence the row could be from a view and view rows
+ Declares a row with the structure of the given table or
+ cursor. <replaceable>table</replaceable> must be an existing
+ table or view name of the database. Cursor must be declared in
+ an enclosing declaration block, or earlier in the same
+ declaration block. The fields of the row are accessed in the
+ dot notation. Parameters to a function can be composite types
+ (complete table rows). In that case, the corresponding
+ identifier $n will be a rowtype, but it must be aliased using
+ the ALIAS command described below. Only the user attributes of
+ a table row are accessible in the row, no Oid or other system
+ attributes (hence the row could be from a view and view rows
don't have useful system attributes).
</para>
<para>
@@ -226,6 +229,85 @@ END;

<varlistentry>
<term>
+CURSOR <replaceable>name</replaceable>
+[(<replaceable>parameters</replaceable>)]
+IS SELECT <replaceable>expression</replaceable>;
+ </term>
+ <listitem>
+ <para>
+ Declares a cursor. A cursor used to return the results of a
+ query one at a time for further processing. Cursors may be
+ used with the FOR statement, or with the OPEN, FETCH, and
+ CLOSE statements. Cursors in PL/pgSQL should not be confused
+ with cursors in SQL; they are very similar, but not
+ identical.
+ </para>
+ <para>
+ A cursor may optionally have comma separated parameters. Each
+ parameter has a name and a type, and an option default value.
+ <replaceable>name</replaceable> [IN]
+ <replaceable>type</replaceable> [:=
+ <replaceable>value</replaceable>]. Parameter values are
+ specified when the cursor is used in a FOR or OPEN statement.
+ </para>
+ <para>
+ Cursors automatically define four additional variables, which
+ have names which start with the name of the cursor:
+ <variablelist>
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable>%ISOPEN
+ </term>
+ <listitem>
+ <para>
+ True if the cursor is open, false otherwise.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable>%FOUND
+ </term>
+ <listitem>
+ <para>
+ True if the last FETCH from a cursor retrieved data.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable>%NOTFOUND
+ </term>
+ <listitem>
+ <para>
+ True if the last FETCH from a cursor did not retrieve any
+ data, because all the data has been exhausted.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable>%ROWCOUNT
+ </term>
+ <listitem>
+ <para>
+ The number of rows fetched from a cursor.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ <para>
+ If a cursor uses the FOR UPDATE clause, then, after doing a
+ FETCH from the cursor, you may use CURRENT OF
+ <replaceable>cursor</replaceable> in a WHERE clause of an
+ UPDATE. This will update the row which was just fetched.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
<replaceable>name</replaceable> ALIAS FOR $n;
</term>
<listitem>
@@ -630,14 +712,14 @@ END LOOP;
the loop. The iteration step is always 1.
<programlisting>
[&lt;&lt;label&gt;&gt;]
-FOR <replaceable>record | row</replaceable> IN <replaceable>select_clause</replaceable> LOOP
+FOR <replaceable>record | row</replaceable> IN <replaceable>select_clause</replaceable> | <replaceable>cursor</replaceable>[(<replaceable>parameter values</replaceable>)] LOOP
<replaceable>statements</replaceable>
END LOOP;
</programlisting>
- The record or row is assigned all the rows resulting from the select
- clause and the statements executed for each. If the loop is terminated
- with an EXIT statement, the last assigned row is still accessible
- after the loop.
+ The record or row is assigned all the rows resulting from the
+ select clause or the cursor and the statements executed for
+ each. If the loop is terminated with an EXIT statement, the
+ last assigned row is still accessible after the loop.
<programlisting>
EXIT [ <replaceable>label</replaceable> ] [ WHEN <replaceable>expression</replaceable> ];
</programlisting>
@@ -653,6 +735,30 @@ EXIT [ <replaceable>label</replaceable>
</listitem>
</varlistentry>

+ <varlistentry>
+ <term>
+Cursor statements
+ </term>
+ <listitem>
+ <para>
+ <programlisting>
+OPEN <replaceable>cursor</replaceable>[(<replaceable>parameter values</replaceable>)];
+ </programlisting>
+ Open a cursor. This must be done before any attempt to FETCH
+ values.
+ <programlisting>
+FETCH <replaceable>cursor</replaceable> INTO <replaceable>record | row | variables</replaceable>;
+ </programlisting>
+ Fetch the current value from a cursor into a record, or a row,
+ or a comma separated list of variables.
+ <programlisting>
+CLOSE <replaceable>cursor</replaceable>;
+ </programlisting>
+ Close a cursor.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>

</sect2>
@@ -940,6 +1046,42 @@ CREATE TRIGGER emp_stamp BEFORE INSERT O
</programlisting>
</para>
</sect2>
+
+ <sect2>
+ <title>Cursor example</title>
+
+ <para>
+ Here is a simple example of stepping through a cursor and using
+ it to update values.
+ <programlisting>
+CREATE FUNCTION cursortest() RETURNS int4 AS '
+ DECLARE
+ CURSOR mycursor(lname VARCHAR(25)) IS SELECT * FROM users
+ WHERE lastname = lname FOR UPDATE;
+ myrec mycursor%ROWTYPE;
+ c int4;
+ BEGIN
+ OPEN mycursor(''Taylor'');
+ c := 0;
+ LOOP
+ FETCH mycursor INTO myrec;
+ IF mycursor%NOTFOUND THEN
+ EXIT;
+ END IF;
+ IF myrec.doupdate THEN
+ UPDATE users SET updatetime = ''now''
+ WHERE CURRENT OF mycursor;
+ c := c + 1;
+ END IF;
+ END LOOP;
+ CLOSE mycursor;
+ RETURN c;
+ END;
+' LANGUAGE 'plpgsql';
+ </programlisting>
+ </para>
+ </sect2>
+
</sect1>
</chapter>

Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.13
diff -p -u -r1.13 gram.y
--- src/pl/plpgsql/src/gram.y 2001/01/06 01:39:01 1.13
+++ src/pl/plpgsql/src/gram.y 2001/01/23 00:07:20
@@ -38,6 +38,7 @@

#include <stdio.h>
#include <string.h>
+#include <ctype.h>
#include "plpgsql.h"
#ifdef YYBISON
#include "pl_scan.c" /* GNU bison wants it here */
@@ -45,9 +46,13 @@



-static PLpgSQL_expr *read_sqlstmt(int until, char *s, char *sqlstart);
+static PLpgSQL_expr *read_sqlstmt(int until, int until2, char *s,
+ char *sqlstart, int *end);
static PLpgSQL_stmt *make_select_stmt(void);
static PLpgSQL_expr *make_tupret_expr(PLpgSQL_row *row);
+static int make_cursor_var(char *cursor, char *attr,
+ int lineno, char *type,
+ PLpgSQL_expr *defval);

%}

@@ -72,12 +77,18 @@ static PLpgSQL_expr *make_tupret_expr(PL
int n_initvars;
int *initvarnos;
} declhdr;
+ struct {
+ int nalloc;
+ int nused;
+ PLpgSQL_expr **exprs;
+ } explist;
PLpgSQL_type *dtype;
PLpgSQL_var *var;
PLpgSQL_row *row;
PLpgSQL_rec *rec;
PLpgSQL_recfield *recfield;
PLpgSQL_trigarg *trigarg;
+ PLpgSQL_cursor *cursor;
PLpgSQL_expr *expr;
PLpgSQL_stmt *stmt;
PLpgSQL_stmts *stmts;
@@ -89,13 +100,16 @@ static PLpgSQL_expr *make_tupret_expr(PL
%type <varname> decl_varname
%type <str> decl_renname
%type <ival> decl_const, decl_notnull, decl_atttypmod, decl_atttypmodval
-%type <expr> decl_defval
-%type <dtype> decl_datatype, decl_dtypename
+%type <expr> decl_defval, decl_cursor_expr, decl_cursor_default
+%type <dtype> decl_datatype, decl_dtypename, decl_cursor_return
%type <row> decl_rowtype
%type <nsitem> decl_aliasitem
%type <str> decl_stmts, decl_stmt
+%type <dtlist> decl_cursor_param_decl, decl_cursor_params
+%type <ival> decl_cursor_param

%type <expr> expr_until_semi, expr_until_then, expr_until_loop
+%type <expr> expr_until_comma_or_paren
%type <expr> opt_exitcond

%type <ival> assign_var
@@ -103,6 +117,9 @@ static PLpgSQL_expr *make_tupret_expr(PL
%type <varname> fori_varname
%type <forilow> fori_lower
%type <rec> fors_target
+%type <cursor> cursor
+%type <explist> cursor_params, cursor_param_vals
+%type <expr> cursor_param

%type <str> opt_lblname, opt_label
%type <str> opt_exitlabel
@@ -112,13 +129,19 @@ static PLpgSQL_expr *make_tupret_expr(PL
%type <stmt> proc_stmt, pl_block
%type <stmt> stmt_assign, stmt_if, stmt_loop, stmt_while, stmt_exit
%type <stmt> stmt_return, stmt_raise, stmt_execsql, stmt_fori
-%type <stmt> stmt_fors, stmt_select, stmt_perform
+%type <stmt> stmt_fors, stmt_forc, stmt_select, stmt_perform
%type <stmt> stmt_dynexecute, stmt_dynfors, stmt_getdiag
+%type <stmt> stmt_open, stmt_fetch, stmt_close

%type <dtlist> raise_params
%type <ival> raise_level, raise_param
%type <str> raise_msg

+%type <dtlist> fetch_vars
+%type <ival> fetch_var
+%type <rec> fetch_record
+%type <row> fetch_row
+
%type <dtlist> getdiag_items, getdiag_targets
%type <ival> getdiag_item, getdiag_target

@@ -130,7 +153,9 @@ static PLpgSQL_expr *make_tupret_expr(PL
%token K_ALIAS
%token K_ASSIGN
%token K_BEGIN
+%token K_CLOSE
%token K_CONSTANT
+%token K_CURSOR
%token K_DEBUG
%token K_DECLARE
%token K_DEFAULT
@@ -141,6 +166,7 @@ static PLpgSQL_expr *make_tupret_expr(PL
%token K_EXCEPTION
%token K_EXECUTE
%token K_EXIT
+%token K_FETCH
%token K_FOR
%token K_FROM
%token K_GET
@@ -151,6 +177,7 @@ static PLpgSQL_expr *make_tupret_expr(PL
%token K_NOT
%token K_NOTICE
%token K_NULL
+%token K_OPEN
%token K_PERFORM
%token K_PROCESSED
%token K_RAISE
@@ -172,6 +199,7 @@ static PLpgSQL_expr *make_tupret_expr(PL
%token T_FUNCTION
%token T_TRIGGER
%token T_CHAR
+%token T_CURSOR
%token T_BPCHAR
%token T_VARCHAR
%token T_LABEL
@@ -347,6 +375,72 @@ decl_statement : decl_varname decl_const
{
plpgsql_ns_rename($2, $4);
}
+ | decl_cursor_start decl_varname decl_cursor_param_decl decl_cursor_return decl_cursor_expr
+ {
+ PLpgSQL_cursor *new;
+ PLpgSQL_expr *defval;
+
+ new = malloc(sizeof(PLpgSQL_cursor));
+ new->dtype = PLPGSQL_DTYPE_CURSOR;
+ new->refname = $2.name;
+ new->lineno = $2.lineno;
+
+ new->select = $5;
+ new->n_params = $3.nused;
+ if ($3.nused == 0)
+ new->params = NULL;
+ else
+ {
+ new->params = malloc($3.nused * sizeof(int));
+ memcpy(new->params, $3.dtnums,
+ $3.nused * sizeof(int));
+ pfree($3.dtnums);
+ }
+
+ new->tuptable = NULL;
+ new->count = 0;
+
+ plpgsql_ns_pop();
+
+ defval = malloc(sizeof(PLpgSQL_expr) - 1);
+ defval->dtype = PLPGSQL_DTYPE_EXPR;
+ defval->query = strdup("SELECT FALSE");
+ defval->plan = NULL;
+ defval->nparams = 0;
+
+ new->found_varno = make_cursor_var($2.name,
+ "found",
+ $2.lineno,
+ "bool",
+ NULL);
+ new->isopen_varno = make_cursor_var($2.name,
+ "isopen",
+ $2.lineno,
+ "bool",
+ defval);
+ new->notfound_varno = make_cursor_var($2.name,
+ "notfound",
+ $2.lineno,
+ "bool",
+ NULL);
+ new->rowcount_varno = make_cursor_var($2.name,
+ "rowcount",
+ $2.lineno,
+ "int4",
+ NULL);
+ new->oid_varno = make_cursor_var($2.name,
+ "oid",
+ $2.lineno,
+ "int4",
+ NULL);
+ new->saw_current_of = false;
+ new->oid_added = false;
+
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_ns_additem(PLPGSQL_NSTYPE_CURSOR,
+ new->cursorno,
+ $2.name);
+ }
;

decl_aliasitem : T_WORD
@@ -515,6 +609,121 @@ decl_defval : ';'
decl_defkey : K_ASSIGN
| K_DEFAULT

+decl_cursor_start : K_CURSOR
+ {
+ plpgsql_ns_push(NULL);
+ /* Note that decl_start called ns_setlocal(true). */
+ }
+ ;
+
+decl_cursor_expr : decl_cursor_is K_SELECT expr_until_semi
+ {
+ $$ = $3;
+ }
+ ;
+
+decl_cursor_param_decl : /* empty */
+ {
+ $$.nalloc = 0;
+ $$.nused = 0;
+ $$.dtnums = NULL;
+ }
+ | '(' decl_cursor_params ')'
+ {
+ $$ = $2;
+ }
+ ;
+
+decl_cursor_params : decl_cursor_param
+ {
+ $$.nalloc = 1;
+ $$.nused = 1;
+ $$.dtnums = palloc(sizeof(int) * $$.nalloc);
+ $$.dtnums[0] = $1;
+ }
+ | decl_cursor_params ',' decl_cursor_param
+ {
+ if ($1.nused >= $1.nalloc)
+ {
+ $1.nalloc *= 2;
+ $1.dtnums = repalloc($1.dtnums,
+ sizeof(int) * $1.nalloc);
+ }
+ $1.dtnums[$1.nused] = $3;
+ ++$1.nused;
+
+ $$ = $1;
+ }
+ ;
+
+decl_cursor_param : decl_varname decl_cursor_optin decl_datatype decl_cursor_default
+ {
+ PLpgSQL_var *new;
+
+ new = malloc(sizeof(PLpgSQL_var));
+
+ new->dtype = PLPGSQL_DTYPE_VAR;
+ new->refname = $1.name;
+ new->lineno = $1.lineno;
+
+ new->datatype = $3;
+ new->isconst = false;
+ new->notnull = false;
+ new->default_val = $4;
+
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_ns_additem(PLPGSQL_NSTYPE_VAR, new->varno,
+ $1.name);
+
+ $$ = new->varno;
+ }
+ ;
+
+decl_cursor_optin : /* empty */
+ | K_IN
+ ;
+
+decl_cursor_default : /* empty */
+ {
+ $$ = NULL;
+ }
+ | decl_defkey expr_until_comma_or_paren
+ {
+ $$ = $2;
+ }
+ ;
+
+decl_cursor_return : /* empty */
+ {
+ $$ = NULL;
+ }
+ | K_RETURN decl_cursor_returntype
+ {
+ yyerror("cursor return type not supported");
+ $$ = NULL;
+ }
+ ;
+
+decl_cursor_returntype : T_ROW
+ | T_DTYPE
+ ;
+
+decl_cursor_is : /* empty */
+ | T_WORD
+ {
+ char *is;
+
+ /* `is' is just a noise word in the syntax.
+ * Avoid making it a keyword by checking for
+ * it here.
+ */
+ is = plpgsql_tolower(yytext);
+ if (strcmp(is, "is") != 0)
+ yyerror("expected IS");
+ pfree(is);
+ }
+ ;
+
proc_sect :
{
PLpgSQL_stmts *new;
@@ -569,6 +778,8 @@ proc_stmt : pl_block
{ $$ = $1; }
| stmt_fors
{ $$ = $1; }
+ | stmt_forc
+ { $$ = $1; }
| stmt_select
{ $$ = $1; }
| stmt_exit
@@ -587,6 +798,12 @@ proc_stmt : pl_block
{ $$ = $1; }
| stmt_getdiag
{ $$ = $1; }
+ | stmt_open
+ { $$ = $1; }
+ | stmt_fetch
+ { $$ = $1; }
+ | stmt_close
+ { $$ = $1; }
;

stmt_perform : K_PERFORM lno expr_until_semi
@@ -994,6 +1211,93 @@ fors_target : T_RECORD
}
;

+stmt_forc : opt_label K_FOR lno fors_target K_IN cursor cursor_params K_LOOP loop_body
+ {
+ PLpgSQL_stmt_forc *new;
+
+ new = malloc(sizeof(PLpgSQL_stmt_forc));
+ memset(new, 0, sizeof(PLpgSQL_stmt_forc));
+
+ new->cmd_type = PLPGSQL_STMT_FORC;
+ new->lineno = $3;
+ new->label = $1;
+ switch ($4->dtype) {
+ case PLPGSQL_DTYPE_REC:
+ new->rec = $4;
+ break;
+ case PLPGSQL_DTYPE_ROW:
+ new->row = (PLpgSQL_row *)$4;
+ break;
+ default:
+ plpgsql_comperrinfo();
+ elog(ERROR, "unknown dtype %d in stmt_forc",
+ $4->dtype);
+ }
+ new->cursor = $6;
+ new->nparams = $7.nused;
+ if ($7.nused == 0)
+ new->params = NULL;
+ else
+ {
+ new->params = malloc($7.nused * sizeof(int));
+ memcpy(new->params, $7.exprs,
+ $7.nused * sizeof(int));
+ pfree($7.exprs);
+ }
+ new->body = $9;
+
+ plpgsql_ns_pop();
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+ ;
+
+cursor : T_CURSOR
+ {
+ $$ = yylval.cursor;
+ }
+ ;
+
+cursor_params : /* empty */
+ {
+ $$.nused = 0;
+ $$.nalloc = 0;
+ $$.exprs = NULL;
+ }
+ | '(' cursor_param_vals ')'
+ {
+ $$ = $2;
+ }
+ ;
+
+cursor_param_vals : cursor_param
+ {
+ $$.nalloc = 1;
+ $$.nused = 1;
+ $$.exprs = palloc($$.nalloc * sizeof(PLpgSQL_expr *));
+ $$.exprs[0] = $1;
+ }
+ | cursor_param_vals ',' cursor_param
+ {
+ if ($1.nused >= $1.nalloc)
+ {
+ $1.nalloc *= 2;
+ $1.exprs = repalloc($1.exprs,
+ $1.nalloc * sizeof(PLpgSQL_expr *));
+ }
+ $1.exprs[$1.nused] = $3;
+ ++$1.nused;
+
+ $$ = $1;
+ }
+ ;
+
+cursor_param : expr_until_comma_or_paren
+ {
+ $$ = $1;
+ }
+ ;
+
stmt_select : K_SELECT lno
{
$$ = make_select_stmt();
@@ -1162,7 +1466,7 @@ stmt_execsql : execsql_start lno
new = malloc(sizeof(PLpgSQL_stmt_execsql));
new->cmd_type = PLPGSQL_STMT_EXECSQL;
new->lineno = $2;
- new->sqlstmt = read_sqlstmt(';', ";", $1);
+ new->sqlstmt = read_sqlstmt(';', ';', ";", $1, NULL);

$$ = (PLpgSQL_stmt *)new;
}
@@ -1187,6 +1491,133 @@ execsql_start : T_WORD
{ $$ = strdup(yytext); }
;

+stmt_open : K_OPEN lno cursor cursor_params ';'
+ {
+ PLpgSQL_stmt_open *new;
+
+ new = malloc(sizeof(PLpgSQL_stmt_open));
+ new->cmd_type = PLPGSQL_STMT_OPEN;
+ new->lineno = $2;
+ new->cursor = $3;
+ new->nparams = $4.nused;
+ if (new->nparams == 0)
+ new->params = NULL;
+ else
+ {
+ new->params = malloc($4.nused * sizeof(int));
+ memcpy(new->params, $4.exprs,
+ $4.nused * sizeof(int));
+ pfree($4.exprs);
+ }
+
+ $$ = (PLpgSQL_stmt *) new;
+ }
+ ;
+
+stmt_fetch : K_FETCH lno cursor K_INTO fetch_vars ';'
+ {
+ PLpgSQL_stmt_fetch *new;
+
+ new = malloc(sizeof(PLpgSQL_stmt_fetch));
+ new->cmd_type = PLPGSQL_STMT_FETCH;
+ new->lineno = $2;
+ new->cursor = $3;
+ new->nvars = $5.nused;
+ new->varnos = malloc($5.nused * sizeof(int));
+ memcpy(new->varnos, $5.dtnums,
+ $5.nused * sizeof(int));
+ pfree($5.dtnums);
+ new->rec = NULL;
+ new->row = NULL;
+
+ $$ = (PLpgSQL_stmt *) new;
+ }
+ | K_FETCH lno cursor K_INTO fetch_record ';'
+ {
+ PLpgSQL_stmt_fetch *new;
+
+ new = malloc(sizeof(PLpgSQL_stmt_fetch));
+ new->cmd_type = PLPGSQL_STMT_FETCH;
+ new->lineno = $2;
+ new->cursor = $3;
+ new->nvars = 0;
+ new->varnos = NULL;
+ new->rec = $5;
+ new->row = NULL;
+
+ $$ = (PLpgSQL_stmt *) new;
+ }
+ | K_FETCH lno cursor K_INTO fetch_row ';'
+ {
+ PLpgSQL_stmt_fetch *new;
+
+ new = malloc(sizeof(PLpgSQL_stmt_fetch));
+ new->cmd_type = PLPGSQL_STMT_FETCH;
+ new->lineno = $2;
+ new->cursor = $3;
+ new->nvars = 0;
+ new->varnos = NULL;
+ new->rec = NULL;
+ new->row = $5;
+
+ $$ = (PLpgSQL_stmt *) new;
+ }
+
+ ;
+
+fetch_record : T_RECORD
+ {
+ $$ = yylval.rec;
+ }
+ ;
+
+fetch_row : T_ROW
+ {
+ $$ = yylval.row;
+ }
+ ;
+
+fetch_vars : fetch_var
+ {
+ $$.nalloc = 1;
+ $$.nused = 1;
+ $$.dtnums = palloc(sizeof(int) * $$.nalloc);
+ $$.dtnums[0] = $1;
+ }
+ | fetch_vars ',' fetch_var
+ {
+ if ($1.nused >= $1.nalloc)
+ {
+ $1.nalloc *= 2;
+ $1.dtnums = repalloc($1.dtnums,
+ sizeof(int) * $1.nalloc);
+ }
+ $1.dtnums[$1.nused] = $3;
+ ++$1.nused;
+
+ $$ = $1;
+ }
+ ;
+
+fetch_var : T_VARIABLE
+ {
+ $$ = yylval.var->varno;
+ }
+ ;
+
+stmt_close : K_CLOSE lno cursor ';'
+ {
+ PLpgSQL_stmt_close *new;
+
+ new = malloc(sizeof(PLpgSQL_stmt_close));
+ new->cmd_type = PLPGSQL_STMT_CLOSE;
+ new->lineno = $2;
+ new->cursor = $3;
+
+ $$ = (PLpgSQL_stmt *) new;
+ }
+ ;
+
expr_until_semi :
{ $$ = plpgsql_read_expression(';', ";"); }
;
@@ -1199,6 +1630,15 @@ expr_until_loop :
{ $$ = plpgsql_read_expression(K_LOOP, "LOOP"); }
;

+expr_until_comma_or_paren :
+ {
+ int end;
+
+ $$ = read_sqlstmt(',', ')', ", or )", "SELECT ", &end);
+ unput(end);
+ }
+ ;
+
opt_label :
{
plpgsql_ns_push(NULL);
@@ -1244,12 +1684,12 @@ lno :
PLpgSQL_expr *
plpgsql_read_expression (int until, char *s)
{
- return read_sqlstmt(until, s, "SELECT ");
+ return read_sqlstmt(until, until, s, "SELECT ", NULL);
}


static PLpgSQL_expr *
-read_sqlstmt (int until, char *s, char *sqlstart)
+read_sqlstmt (int until, int until2, char *s, char *sqlstart, int *end)
{
int tok;
int lno;
@@ -1258,12 +1698,14 @@ read_sqlstmt (int until, char *s, char *
int params[1024];
char buf[32];
PLpgSQL_expr *expr;
+ int current_of_state = 0;
+ char c;

lno = yylineno;
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, sqlstart);

- while((tok = yylex()) != until) {
+ while((tok = yylex()) != until && tok != until2) {
if (tok == ';') break;
if (plpgsql_SpaceScanned) {
plpgsql_dstring_append(&ds, " ");
@@ -1273,20 +1715,58 @@ read_sqlstmt (int until, char *s, char *
params[nparams] = yylval.var->varno;
sprintf(buf, " $%d ", ++nparams);
plpgsql_dstring_append(&ds, buf);
+ current_of_state = 0;
break;

case T_RECFIELD:
params[nparams] = yylval.recfield->rfno;
sprintf(buf, " $%d ", ++nparams);
plpgsql_dstring_append(&ds, buf);
+ current_of_state = 0;
break;

case T_TGARGV:
params[nparams] = yylval.trigarg->dno;
sprintf(buf, " $%d ", ++nparams);
plpgsql_dstring_append(&ds, buf);
+ current_of_state = 0;
break;

+ case T_CURSOR:
+ /* Look specially for ``CURRENT OF cursor'', and
+ * convert it into a reference to the cursor OID
+ * variable. This is a real hack, but I don't think
+ * there is any other way to do it short of parsing
+ * the whole statement here.
+ */
+ if (current_of_state == 2)
+ {
+ char *str;
+ char *cp;
+
+ /* Whitespace is stripped by the lexer, so we can
+ * use single spaces here.
+ */
+ str = "current of ";
+ cp = plpgsql_tolower(plpgsql_dstring_get(&ds));
+ if (strcmp(cp + ds.used - strlen(str), str) != 0)
+ elog(ERROR, "read_sqlstmt: internal error");
+ pfree(cp);
+
+ ds.used -= strlen(str);
+ plpgsql_dstring_append(&ds, "oid = ");
+ params[nparams] = yylval.cursor->oid_varno;
+ sprintf(buf, " $%d", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+
+ yylval.cursor->saw_current_of = true;
+
+ break;
+ }
+ plpgsql_dstring_append(&ds, yytext);
+ current_of_state = 0;
+ break;
+
default:
if (tok == 0) {
plpgsql_error_lineno = lno;
@@ -1294,10 +1774,34 @@ read_sqlstmt (int until, char *s, char *
elog(ERROR, "missing %s at end of SQL statement", s);
}
plpgsql_dstring_append(&ds, yytext);
+
+ c = yytext[0];
+ if (isupper(c))
+ c = tolower(c);
+ if ((current_of_state == 0
+ && c == 'c')
+ || (current_of_state == 1
+ && c == 'o'))
+ {
+ if (current_of_state == 0
+ && strcasecmp(yytext, "current") == 0)
+ current_of_state = 1;
+ else if (current_of_state == 1
+ && strcasecmp(yytext, "of") == 0)
+ current_of_state = 2;
+ else
+ current_of_state = 0;
+ }
+ else
+ current_of_state = 0;
+
break;
}
}

+ if (end != NULL)
+ *end = tok;
+
expr = malloc(sizeof(PLpgSQL_expr) + sizeof(int) * nparams - 1);
expr->dtype = PLPGSQL_DTYPE_EXPR;
expr->query = strdup(plpgsql_dstring_get(&ds));
@@ -1613,4 +2117,38 @@ make_tupret_expr(PLpgSQL_row *row)

plpgsql_dstring_free(&ds);
return expr;
+}
+
+
+/* Cursor attributes are handled by defining variables with magic
+ * names.
+ */
+static int
+make_cursor_var(char *cursor, char *attr, int lineno, char *type,
+ PLpgSQL_expr *defval)
+{
+ char *s;
+ PLpgSQL_var *new;
+
+ s = malloc(strlen(cursor) + strlen(attr) + 2);
+ sprintf(s, "%s%%%s", cursor, attr);
+
+ new = malloc(sizeof(PLpgSQL_var));
+
+ new->dtype = PLPGSQL_DTYPE_VAR;
+ new->refname = s;
+ new->lineno = lineno;
+
+ if (plpgsql_parse_word(type) != T_DTYPE)
+ elog(ERROR, "internal error: '%s' is not a type", type);
+
+ new->datatype = yylval.dtype;
+ new->isconst = false;
+ new->notnull = false;
+ new->default_val = defval;
+
+ plpgsql_adddatum((PLpgSQL_datum *)new);
+ plpgsql_ns_additem(PLPGSQL_NSTYPE_VAR, new->varno, s);
+
+ return new->varno;
}
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.25
diff -p -u -r1.25 pl_comp.c
--- src/pl/plpgsql/src/pl_comp.c 2000/12/08 00:03:02 1.25
+++ src/pl/plpgsql/src/pl_comp.c 2001/01/23 00:07:20
@@ -607,6 +607,10 @@ plpgsql_parse_word(char *word)
plpgsql_yylval.row = (PLpgSQL_row *) (plpgsql_Datums[nse->itemno]);
return T_ROW;

+ case PLPGSQL_NSTYPE_CURSOR:
+ plpgsql_yylval.cursor = (PLpgSQL_cursor *) (plpgsql_Datums[nse->itemno]);
+ return T_CURSOR;
+
default:
return T_ERROR;
}
@@ -1135,12 +1139,13 @@ plpgsql_parse_dblwordtype(char *string)

/* ----------
* plpgsql_parse_wordrowtype Scanner found word%ROWTYPE.
- * So word must be a table name.
+ * So word must be a table name or a cursor.
* ----------
*/
int
plpgsql_parse_wordrowtype(char *string)
{
+ PLpgSQL_nsitem *nse;
HeapTuple classtup;
Form_pg_class classStruct;
HeapTuple typetup;
@@ -1154,13 +1159,26 @@ plpgsql_parse_wordrowtype(char *string)
PLpgSQL_var *var;

/* ----------
- * Get the word in lower case and fetch the pg_class tuple.
+ * Get the word in lower case and fetch the cursor or pg_class tuple.
* ----------
*/
word1 = plpgsql_tolower(string);
cp = strchr(word1, '%');
*cp = '\0';

+ nse = plpgsql_ns_lookup(word1, NULL);
+ if (nse != NULL && nse->itemtype == PLPGSQL_NSTYPE_CURSOR)
+ {
+ /* We only accept cursor%ROWTYPE in a declaration. And for
+ * simplicity we don't care about the type of the record--we
+ * just look up the fields when we have their names. So we
+ * treat this as though it were the record keyword. This
+ * works correctly, though it doesn't permit much error
+ * checking.
+ */
+ return K_RECORD;
+ }
+
classtup = SearchSysCache(RELNAME,
PointerGetDatum(word1),
0, 0, 0);
@@ -1293,10 +1311,27 @@ plpgsql_parse_wordrowtype(char *string)


/* ----------
- * plpgsql_adddatum Add a variable, record or row
- * to the compilers datum list.
+ * plpgsql_parse_attribute Parse a cursor attribute.
* ----------
*/
+int
+plpgsql_parse_attribute(char *string)
+{
+ /* Cursor attributes are entered into the namespace tables with an
+ * embedded %, so they look exactly like the strings the user
+ * uses. This makes this function easy.
+ */
+
+ if (plpgsql_parse_word(string) != T_VARIABLE)
+ return T_ERROR;
+ return T_VARIABLE;
+}
+
+
+/* ----------
+ * plpgsql_adddatum Add a variable, record or row
+ * to the compilers datum list.
+ * ---------- */
void
plpgsql_adddatum(PLpgSQL_datum * new)
{
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.36
diff -p -u -r1.36 pl_exec.c
--- src/pl/plpgsql/src/pl_exec.c 2001/01/22 00:50:07 1.36
+++ src/pl/plpgsql/src/pl_exec.c 2001/01/23 00:07:21
@@ -72,6 +72,7 @@ static char *error_info_text = NULL;
************************************************************/
static PLpgSQL_var *copy_var(PLpgSQL_var * var);
static PLpgSQL_rec *copy_rec(PLpgSQL_rec * rec);
+static PLpgSQL_cursor *copy_cursor(PLpgSQL_cursor * cursor);

static int exec_stmt_block(PLpgSQL_execstate * estate,
PLpgSQL_stmt_block * block);
@@ -91,8 +92,13 @@ static int exec_stmt_while(PLpgSQL_execs
PLpgSQL_stmt_while * stmt);
static int exec_stmt_fori(PLpgSQL_execstate * estate,
PLpgSQL_stmt_fori * stmt);
+static int exec_stmt_forsc(PLpgSQL_execstate * estate, char * label,
+ PLpgSQL_rec * rec, PLpgSQL_row * row, PLpgSQL_expr * query,
+ PLpgSQL_stmts * body, int skip);
static int exec_stmt_fors(PLpgSQL_execstate * estate,
PLpgSQL_stmt_fors * stmt);
+static int exec_stmt_forc(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_forc * stmt);
static int exec_stmt_select(PLpgSQL_execstate * estate,
PLpgSQL_stmt_select * stmt);
static int exec_stmt_exit(PLpgSQL_execstate * estate,
@@ -107,6 +113,12 @@ static int exec_stmt_dynexecute(PLpgSQL_
PLpgSQL_stmt_dynexecute * stmt);
static int exec_stmt_dynfors(PLpgSQL_execstate * estate,
PLpgSQL_stmt_dynfors * stmt);
+static int exec_stmt_open(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_open * stmt);
+static int exec_stmt_fetch(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_fetch * stmt);
+static int exec_stmt_close(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_close * stmt);

static void exec_prepare_plan(PLpgSQL_execstate * estate,
PLpgSQL_expr * expr);
@@ -132,7 +144,7 @@ static int exec_run_select(PLpgSQL_execs
static void exec_move_row(PLpgSQL_execstate * estate,
PLpgSQL_rec * rec,
PLpgSQL_row * row,
- HeapTuple tup, TupleDesc tupdesc);
+ HeapTuple tup, TupleDesc tupdesc, int skip);
static Datum exec_cast_value(Datum value, Oid valtype,
Oid reqtype,
FmgrInfo *reqinput,
@@ -140,6 +152,10 @@ static Datum exec_cast_value(Datum value
int32 reqtypmod,
bool *isnull);
static void exec_set_found(PLpgSQL_execstate * estate, bool state);
+static void exec_set_cursor_params(PLpgSQL_execstate * estate,
+ PLpgSQL_cursor * cursor, int nparams,
+ PLpgSQL_expr ** params);
+static void exec_cursor_add_oid(PLpgSQL_cursor * cursor);


/* ----------
@@ -213,6 +229,9 @@ plpgsql_exec_function(PLpgSQL_function *
case PLPGSQL_STMT_FORS:
stmttype = "for over select rows";
break;
+ case PLPGSQL_STMT_FORC:
+ stmttype = "for over cursor";
+ break;
case PLPGSQL_STMT_SELECT:
stmttype = "select into variables";
break;
@@ -234,6 +253,15 @@ plpgsql_exec_function(PLpgSQL_function *
case PLPGSQL_STMT_DYNFORS:
stmttype = "for over execute statement";
break;
+ case PLPGSQL_STMT_OPEN:
+ stmttype = "open";
+ break;
+ case PLPGSQL_STMT_FETCH:
+ stmttype = "fetch";
+ break;
+ case PLPGSQL_STMT_CLOSE:
+ stmttype = "close";
+ break;
default:
stmttype = "unknown";
break;
@@ -291,6 +319,10 @@ plpgsql_exec_function(PLpgSQL_function *
copy_rec((PLpgSQL_rec *) (func->datums[i]));
break;

+ case PLPGSQL_DTYPE_CURSOR:
+ estate.datums[i] = (PLpgSQL_datum *)
+ copy_cursor((PLpgSQL_cursor *) (func->datums[i]));
+
case PLPGSQL_DTYPE_ROW:
case PLPGSQL_DTYPE_RECFIELD:
estate.datums[i] = func->datums[i];
@@ -333,7 +365,7 @@ plpgsql_exec_function(PLpgSQL_function *
Assert(slot != NULL && ! fcinfo->argnull[i]);
tup = slot->val;
tupdesc = slot->ttc_tupleDescriptor;
- exec_move_row(&estate, NULL, row, tup, tupdesc);
+ exec_move_row(&estate, NULL, row, tup, tupdesc, 0);
}
break;

@@ -363,6 +395,16 @@ plpgsql_exec_function(PLpgSQL_function *
}
break;

+ case PLPGSQL_DTYPE_CURSOR:
+ {
+ PLpgSQL_cursor *cur = (PLpgSQL_cursor *) estate.datums[i];
+
+ cur->tuptable = NULL;
+ cur->count = 0;
+ cur->index = -1;
+ }
+ break;
+
case PLPGSQL_DTYPE_ROW:
case PLPGSQL_DTYPE_REC:
case PLPGSQL_DTYPE_RECFIELD:
@@ -525,6 +567,9 @@ plpgsql_exec_trigger(PLpgSQL_function *
case PLPGSQL_STMT_FORS:
stmttype = "for over select rows";
break;
+ case PLPGSQL_STMT_FORC:
+ stmttype = "for over cursor";
+ break;
case PLPGSQL_STMT_SELECT:
stmttype = "select into variables";
break;
@@ -546,6 +591,15 @@ plpgsql_exec_trigger(PLpgSQL_function *
case PLPGSQL_STMT_DYNFORS:
stmttype = "for over execute statement";
break;
+ case PLPGSQL_STMT_OPEN:
+ stmttype = "open";
+ break;
+ case PLPGSQL_STMT_FETCH:
+ stmttype = "fetch";
+ break;
+ case PLPGSQL_STMT_CLOSE:
+ stmttype = "close";
+ break;
default:
stmttype = "unknown";
break;
@@ -603,6 +657,11 @@ plpgsql_exec_trigger(PLpgSQL_function *
copy_rec((PLpgSQL_rec *) (func->datums[i]));
break;

+ case PLPGSQL_DTYPE_CURSOR:
+ estate.datums[i] = (PLpgSQL_datum *)
+ copy_cursor((PLpgSQL_cursor *) (func->datums[i]));
+ break;
+
case PLPGSQL_DTYPE_ROW:
case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_TRIGARG:
@@ -733,6 +792,16 @@ plpgsql_exec_trigger(PLpgSQL_function *
}
break;

+ case PLPGSQL_DTYPE_CURSOR:
+ {
+ PLpgSQL_cursor *cur = (PLpgSQL_cursor *) estate.datums[i];
+
+ cur->tuptable = NULL;
+ cur->count = 0;
+ cur->index = -1;
+ }
+ break;
+
case PLPGSQL_DTYPE_ROW:
case PLPGSQL_DTYPE_REC:
case PLPGSQL_DTYPE_RECFIELD:
@@ -835,7 +904,17 @@ copy_rec(PLpgSQL_rec * rec)
return new;
}

+static PLpgSQL_cursor *
+copy_cursor(PLpgSQL_cursor * cursor)
+{
+ PLpgSQL_cursor *new = palloc(sizeof(PLpgSQL_cursor));
+
+ memcpy(new, cursor, sizeof(PLpgSQL_cursor));
+
+ return new;
+}

+
/* ----------
* exec_stmt_block Execute a block of statements
* ----------
@@ -888,6 +967,16 @@ exec_stmt_block(PLpgSQL_execstate * esta
}
break;

+ case PLPGSQL_DTYPE_CURSOR:
+ {
+ PLpgSQL_cursor *cur = (PLpgSQL_cursor *) estate->datums[n];
+
+ cur->tuptable = NULL;
+ cur->count = 0;
+ cur->index = -1;
+ }
+ break;
+
case PLPGSQL_DTYPE_RECFIELD:
break;

@@ -1003,6 +1092,10 @@ exec_stmt(PLpgSQL_execstate * estate, PL
rc = exec_stmt_fors(estate, (PLpgSQL_stmt_fors *) stmt);
break;

+ case PLPGSQL_STMT_FORC:
+ rc = exec_stmt_forc(estate, (PLpgSQL_stmt_forc *) stmt);
+ break;
+
case PLPGSQL_STMT_SELECT:
rc = exec_stmt_select(estate, (PLpgSQL_stmt_select *) stmt);
break;
@@ -1031,6 +1124,18 @@ exec_stmt(PLpgSQL_execstate * estate, PL
rc = exec_stmt_dynfors(estate, (PLpgSQL_stmt_dynfors *) stmt);
break;

+ case PLPGSQL_STMT_OPEN:
+ rc = exec_stmt_open(estate, (PLpgSQL_stmt_open *) stmt);
+ break;
+
+ case PLPGSQL_STMT_FETCH:
+ rc = exec_stmt_fetch(estate, (PLpgSQL_stmt_fetch *) stmt);
+ break;
+
+ case PLPGSQL_STMT_CLOSE:
+ rc = exec_stmt_close(estate, (PLpgSQL_stmt_close *) stmt);
+ break;
+
default:
error_info_stmt = save_estmt;
elog(ERROR, "unknown cmdtype %d in exec_stmt",
@@ -1335,17 +1440,17 @@ exec_stmt_fori(PLpgSQL_execstate * estat


/* ----------
- * exec_stmt_fors Execute a query, assign each
+ * exec_stmt_forsc Execute a query, assign each
* tuple to a record or row and
* execute a group of statements
* for it.
* ----------
*/
static int
-exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
+exec_stmt_forsc(PLpgSQL_execstate *estate, char *label, PLpgSQL_rec *rec,
+ PLpgSQL_row *row, PLpgSQL_expr *query,
+ PLpgSQL_stmts *body, int skip)
{
- PLpgSQL_rec *rec = NULL;
- PLpgSQL_row *row = NULL;
SPITupleTable *tuptab;
int rc;
int i;
@@ -1358,24 +1463,10 @@ exec_stmt_fors(PLpgSQL_execstate * estat
exec_set_found(estate, false);

/* ----------
- * Determine if we assign to 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 in exec_stmt_fors()");
- }
-
- /* ----------
* Run the query
* ----------
*/
- exec_run_select(estate, stmt->query, 0);
+ exec_run_select(estate, query, 0);
n = SPI_processed;

/* ----------
@@ -1385,7 +1476,7 @@ exec_stmt_fors(PLpgSQL_execstate * estat
*/
if (n == 0)
{
- exec_move_row(estate, rec, row, NULL, NULL);
+ exec_move_row(estate, rec, row, NULL, NULL, 0);
return PLPGSQL_RC_OK;
}

@@ -1408,13 +1499,14 @@ exec_stmt_fors(PLpgSQL_execstate * estat
* Assign the tuple to the target
* ----------
*/
- exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc);
+ exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc,
+ skip);

/* ----------
* Execute the statements
* ----------
*/
- rc = exec_stmts(estate, stmt->body);
+ rc = exec_stmts(estate, body);

/* ----------
* Check returncode
@@ -1428,9 +1520,9 @@ exec_stmt_fors(PLpgSQL_execstate * estat
case PLPGSQL_RC_EXIT:
if (estate->exitlabel == NULL)
return PLPGSQL_RC_OK;
- if (stmt->label == NULL)
+ if (label == NULL)
return PLPGSQL_RC_EXIT;
- if (strcmp(stmt->label, estate->exitlabel))
+ if (strcmp(label, estate->exitlabel))
return PLPGSQL_RC_EXIT;
estate->exitlabel = NULL;
return PLPGSQL_RC_OK;
@@ -1448,6 +1540,83 @@ exec_stmt_fors(PLpgSQL_execstate * estat


/* ----------
+ * exec_stmt_fors Execute a query, assign each
+ * tuple to a record or row and
+ * execute a group of statements
+ * for it.
+ * ----------
+ */
+static int
+exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
+{
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
+
+ /* ----------
+ * Determine if we assign to 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 in exec_stmt_fors()");
+ }
+
+ return exec_stmt_forsc(estate, stmt->label, rec, row, stmt->query,
+ stmt->body, 0);
+}
+
+/* ----------
+ * exec_stmt_forc Evaluate a cursor, assign each
+ * tuple to a record or row and
+ * execute a group of statements
+ * for it.
+ * ----------
+ */
+static int
+exec_stmt_forc(PLpgSQL_execstate * estate, PLpgSQL_stmt_forc * stmt)
+{
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
+ PLpgSQL_var *var;
+ int ret;
+
+ /* ----------
+ * Determine if we assign to 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 in exec_stmt_forc()");
+ }
+
+ exec_set_cursor_params(estate, stmt->cursor, stmt->nparams, stmt->params);
+ if (stmt->cursor->saw_current_of && ! stmt->cursor->oid_added)
+ exec_cursor_add_oid(stmt->cursor);
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->isopen_varno]);
+ var->value = (Datum) true;
+ var->isnull = false;
+
+ ret = exec_stmt_forsc(estate, stmt->label, rec, row,
+ stmt->cursor->select, stmt->body,
+ stmt->cursor->oid_added ? 1 : 0);
+
+ var->value = (Datum) false;
+
+ return ret;
+}
+
+/* ----------
* exec_stmt_select Run a query and assign the first
* row to a record or rowtype.
* ----------
@@ -1494,7 +1663,7 @@ exec_stmt_select(PLpgSQL_execstate * est
*/
if (n == 0)
{
- exec_move_row(estate, rec, row, NULL, NULL);
+ exec_move_row(estate, rec, row, NULL, NULL, 0);
return PLPGSQL_RC_OK;
}

@@ -1505,7 +1674,7 @@ exec_stmt_select(PLpgSQL_execstate * est
tuptab = SPI_tuptable;
SPI_tuptable = NULL;

- exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
+ exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc, 0);

exec_set_found(estate, true);

@@ -2110,7 +2279,7 @@ exec_stmt_dynfors(PLpgSQL_execstate * es
*/
if (n == 0)
{
- exec_move_row(estate, rec, row, NULL, NULL);
+ exec_move_row(estate, rec, row, NULL, NULL, 0);
return PLPGSQL_RC_OK;
}

@@ -2133,7 +2302,7 @@ exec_stmt_dynfors(PLpgSQL_execstate * es
* Assign the tuple to the target
* ----------
*/
- exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc);
+ exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc, 0);

/* ----------
* Execute the statements
@@ -2173,10 +2342,228 @@ exec_stmt_dynfors(PLpgSQL_execstate * es


/* ----------
- * exec_assign_expr Put an expressions result into
- * a variable.
+ * exec_stmt_open Execute an OPEN statement.
* ----------
*/
+static int
+exec_stmt_open(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_open * stmt)
+{
+ PLpgSQL_var *var;
+
+ if (stmt->cursor->index >= 0)
+ elog(ERROR, "Attempt to open cursor `%s' when it is already open",
+ stmt->cursor->refname);
+
+ exec_set_cursor_params(estate, stmt->cursor, stmt->nparams, stmt->params);
+ if (stmt->cursor->saw_current_of && ! stmt->cursor->oid_added)
+ exec_cursor_add_oid(stmt->cursor);
+
+ exec_set_found(estate, false);
+
+ /* If and when SPI supports cursors, we should use a SQL cursor
+ * here, rather than doing the whole query at once.
+ */
+
+ exec_run_select(estate, stmt->cursor->select, 0);
+ stmt->cursor->tuptable = SPI_tuptable;
+ stmt->cursor->count = SPI_processed;
+ SPI_tuptable = NULL;
+
+ stmt->cursor->index = 0;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->found_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->isopen_varno]);
+ var->value = (Datum) true;
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->notfound_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->rowcount_varno]);
+ var->value = Int32GetDatum(0);
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->oid_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ if (SPI_processed > 0)
+ exec_set_found(estate, true);
+
+ return PLPGSQL_RC_OK;
+}
+
+
+/* ----------
+ * exec_stmt_fetch Execute a FETCH statement.
+ * ----------
+ */
+static int
+exec_stmt_fetch(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_fetch * stmt)
+{
+ PLpgSQL_var *var;
+ HeapTuple tup;
+ TupleDesc tupdesc;
+ int skip;
+
+ if (stmt->cursor->index < 0)
+ elog(ERROR, "FETCH from closed cursor");
+
+ if (stmt->cursor->index >= stmt->cursor->count)
+ {
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->found_varno]);
+ var->value = (Datum) false;
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->notfound_varno]);
+ var->value = (Datum) true;
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->oid_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ return PLPGSQL_RC_OK;
+ }
+
+ tup = stmt->cursor->tuptable->vals[stmt->cursor->index];
+ tupdesc = stmt->cursor->tuptable->tupdesc;
+
+ skip = stmt->cursor->oid_added ? 1 : 0;
+
+ if (stmt->nvars == 0)
+ {
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
+
+ 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 in exec_stmt_fetch()");
+ }
+
+ exec_move_row(estate, rec, row, tup, tupdesc, skip);
+ }
+ else
+ {
+ int i;
+
+ if (! HeapTupleIsValid(tup))
+ {
+ /* Can this happen? I'm not sure. */
+ for (i = 0; i < stmt->nvars; ++i)
+ {
+ int varno;
+ bool nullval = true;
+
+ varno = stmt->varnos[i];
+ exec_assign_value(estate, estate->datums[varno],
+ (Datum) 0, 0, &nullval);
+ }
+ }
+ else
+ {
+ if (stmt->nvars != tup->t_data->t_natts - skip)
+ elog(ERROR,
+ "Number of elements in FETCH (%d) does not match number of target variables (%d)",
+ tup->t_data->t_natts,
+ stmt->nvars);
+
+ for (i = 0; i < stmt->nvars; ++i)
+ {
+ Datum value;
+ Oid valtype;
+ bool isnull;
+
+ value = SPI_getbinval(tup, tupdesc, i + 1 + skip, &isnull);
+ valtype = SPI_gettypeid(tupdesc, i + 1 + skip);
+ exec_assign_value(estate, estate->datums[stmt->varnos[i]],
+ value, valtype, &isnull);
+ }
+ }
+ }
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->found_varno]);
+ var->value = (Datum) true;
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->notfound_varno]);
+ var->value = (Datum) false;
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->rowcount_varno]);
+ var->value = Int32GetDatum(DatumGetInt32(var->value) + 1);
+
+ if (stmt->cursor->oid_added)
+ {
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->oid_varno]);
+ var->value = SPI_getbinval(tup, tupdesc, 1, &var->isnull);
+ elog(DEBUG, "exec_stmt_fetch: OID is %d", DatumGetInt32(var->value));
+ }
+
+ ++stmt->cursor->index;
+
+ return PLPGSQL_RC_OK;
+}
+
+
+/* ----------
+ * exec_stmt_close Execute a CLOSE statement.
+ * ----------
+ */
+static int
+exec_stmt_close(PLpgSQL_execstate * estate,
+ PLpgSQL_stmt_close * stmt)
+{
+ PLpgSQL_var *var;
+
+ /* We could free the tuple table here if we know how. If and when
+ * SPI supports cursors, and OPEN and FETCH are changed to use
+ * cursors, then this is where we would close the cursor.
+ */
+
+ stmt->cursor->tuptable = NULL;
+ stmt->cursor->count = 0;
+ stmt->cursor->index = -1;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->found_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->isopen_varno]);
+ var->value = (Datum) false;
+ var->isnull = false;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->notfound_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->rowcount_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ var = (PLpgSQL_var *) (estate->datums[stmt->cursor->oid_varno]);
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ return PLPGSQL_RC_OK;
+}
+
+
+/* ----------
+ * exec_assign_expr Put an expressions result into
+ * a variable.
+ * ---------- */
static void
exec_assign_expr(PLpgSQL_execstate * estate, PLpgSQL_datum * target,
PLpgSQL_expr * expr)
@@ -2649,7 +3036,7 @@ static void
exec_move_row(PLpgSQL_execstate * estate,
PLpgSQL_rec * rec,
PLpgSQL_row * row,
- HeapTuple tup, TupleDesc tupdesc)
+ HeapTuple tup, TupleDesc tupdesc, int skip)
{
PLpgSQL_var *var;
int i;
@@ -2664,6 +3051,10 @@ exec_move_row(PLpgSQL_execstate * estate
*/
if (rec != NULL)
{
+ /* We go ahead and do this even if skip != 0. The tuple
+ * descriptor will let the user pick out the right fields
+ * anyhow.
+ */
if (HeapTupleIsValid(tup))
{
rec->tup = tup;
@@ -2688,7 +3079,7 @@ exec_move_row(PLpgSQL_execstate * estate
{
if (HeapTupleIsValid(tup))
{
- if (row->nfields != tup->t_data->t_natts)
+ if (row->nfields != tup->t_data->t_natts - skip)
{
elog(ERROR, "query didn't return correct # of attributes for %s",
row->refname);
@@ -2698,8 +3089,8 @@ exec_move_row(PLpgSQL_execstate * estate
{
var = (PLpgSQL_var *) (estate->datums[row->varnos[i]]);

- valtype = SPI_gettypeid(tupdesc, i + 1);
- value = SPI_getbinval(tup, tupdesc, i + 1, &isnull);
+ valtype = SPI_gettypeid(tupdesc, i + 1 + skip);
+ value = SPI_getbinval(tup, tupdesc, i + 1 + skip, &isnull);
exec_assign_value(estate, estate->datums[row->varnos[i]],
value, valtype, &isnull);

@@ -2914,4 +3305,96 @@ exec_set_found(PLpgSQL_execstate * estat
var = (PLpgSQL_var *) (estate->datums[estate->found_varno]);
var->value = (Datum) state;
var->isnull = false;
+}
+
+
+/* ----------
+ * exec_set_cursor_params Set cursor parameters
+ * ----------
+ */
+static void
+exec_set_cursor_params(PLpgSQL_execstate * estate, PLpgSQL_cursor * cursor,
+ int nparams, PLpgSQL_expr ** params)
+{
+ int i;
+
+ for (i = 0; i < cursor->n_params; ++i)
+ {
+ int n;
+ PLpgSQL_var *var;
+
+ n = cursor->params[i];
+
+ if (estate->datums[n]->dtype != PLPGSQL_DTYPE_VAR)
+ elog(ERROR, "unknown dtype %d in exec_set_cursor_params()",
+ estate->datums[n]->dtype);
+
+ var = (PLpgSQL_var *) (estate->datums[n]);
+
+ if (i < nparams)
+ exec_assign_expr(estate, (PLpgSQL_datum *) var, params[i]);
+ else if (var->default_val != NULL)
+ exec_assign_expr(estate, (PLpgSQL_datum *) var, var->default_val);
+ else
+ {
+ var->value = (Datum) 0;
+ var->isnull = true;
+ if (var->notnull)
+ elog(ERROR,
+ "cursor parameter '%s' declared NOT NULL cannot default to NULL",
+ var->refname);
+ }
+ }
+}
+
+/* ----------
+ * exec_cursor_add_oid If CURRENT OF is used with a cursor,
+ * add oid as the first field that we retrieve.
+ * ----------
+ */
+static void
+exec_cursor_add_oid(PLpgSQL_cursor * cursor)
+{
+ char *s;
+ char *new;
+
+ if (! cursor->saw_current_of || cursor->oid_added)
+ return;
+
+ s = cursor->select->query;
+ if (strncasecmp(s, "select ", 7) != 0)
+ elog(ERROR, "exec_cursor_add_oid: internal error: bad start");
+ s += 7;
+
+ if (strncasecmp(s, "distinct on ", 12) == 0)
+ {
+ s = strchr(s, ')');
+ if (s == NULL)
+ elog(ERROR, "syntax error in select distinct on clause");
+ ++s;
+ if (*s == ' ')
+ ++s;
+ }
+ else if (strncasecmp(s, "distinct ", 9) == 0)
+ s += 9;
+ else if (strncasecmp(s, "all ", 4) == 0)
+ s += 4;
+
+ /* This is too simple, because if the select is over multiple
+ * tables, oid will be ambiguous. In that case, we need to figure
+ * out which tables the select is over, and add an oid field for
+ * each one. This is also too simple in that it does not permit
+ * UNION, etc. If we ever fix this, note that we only need the
+ * oid for tables which are selected FOR UPDATE.
+ */
+
+ new = malloc(strlen(cursor->select->query) + 10);
+ strncpy(new, cursor->select->query, s - cursor->select->query);
+ strcpy(new + (s - cursor->select->query), "oid, ");
+ strcat(new, s);
+
+ free(cursor->select->query);
+ cursor->select->query = new;
+
+ cursor->oid_added = true;
}
Index: src/pl/plpgsql/src/pl_funcs.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/pl_funcs.c,v
retrieving revision 1.9
diff -p -u -r1.9 pl_funcs.c
--- src/pl/plpgsql/src/pl_funcs.c 2000/12/03 20:45:40 1.9
+++ src/pl/plpgsql/src/pl_funcs.c 2001/01/23 00:07:21
@@ -383,6 +383,7 @@ static void dump_loop(PLpgSQL_stmt_loop
static void dump_while(PLpgSQL_stmt_while * stmt);
static void dump_fori(PLpgSQL_stmt_fori * stmt);
static void dump_fors(PLpgSQL_stmt_fors * stmt);
+static void dump_forc(PLpgSQL_stmt_forc * stmt);
static void dump_select(PLpgSQL_stmt_select * stmt);
static void dump_exit(PLpgSQL_stmt_exit * stmt);
static void dump_return(PLpgSQL_stmt_return * stmt);
@@ -390,6 +391,9 @@ static void dump_raise(PLpgSQL_stmt_rais
static void dump_execsql(PLpgSQL_stmt_execsql * stmt);
static void dump_dynexecute(PLpgSQL_stmt_dynexecute * stmt);
static void dump_dynfors(PLpgSQL_stmt_dynfors * stmt);
+static void dump_open(PLpgSQL_stmt_open * stmt);
+static void dump_fetch(PLpgSQL_stmt_fetch * stmt);
+static void dump_close(PLpgSQL_stmt_close * stmt);
static void dump_getdiag(PLpgSQL_stmt_getdiag * stmt);
static void dump_expr(PLpgSQL_expr * expr);

@@ -430,6 +434,9 @@ dump_stmt(PLpgSQL_stmt * stmt)
case PLPGSQL_STMT_FORS:
dump_fors((PLpgSQL_stmt_fors *) stmt);
break;
+ case PLPGSQL_STMT_FORC:
+ dump_forc((PLpgSQL_stmt_forc *) stmt);
+ break;
case PLPGSQL_STMT_SELECT:
dump_select((PLpgSQL_stmt_select *) stmt);
break;
@@ -451,6 +458,15 @@ dump_stmt(PLpgSQL_stmt * stmt)
case PLPGSQL_STMT_DYNFORS:
dump_dynfors((PLpgSQL_stmt_dynfors *) stmt);
break;
+ case PLPGSQL_STMT_OPEN:
+ dump_open((PLpgSQL_stmt_open *) stmt);
+ break;
+ case PLPGSQL_STMT_FETCH:
+ dump_fetch((PLpgSQL_stmt_fetch *) stmt);
+ break;
+ case PLPGSQL_STMT_CLOSE:
+ dump_close((PLpgSQL_stmt_close *) stmt);
+ break;
case PLPGSQL_STMT_GETDIAG:
dump_getdiag((PLpgSQL_stmt_getdiag *) stmt);
break;
@@ -601,6 +617,39 @@ dump_fors(PLpgSQL_stmt_fors * stmt)
}

static void
+dump_forc(PLpgSQL_stmt_forc * stmt)
+{
+ int i;
+
+ dump_ind();
+ printf("FORC %s IN %s",
+ (stmt->rec != NULL) ? stmt->rec->refname : stmt->row->refname,
+ stmt->cursor->refname);
+ if (stmt->nparams > 0)
+ {
+ int i;
+
+ printf("(");
+ for (i = 0; i < stmt->nparams; ++i)
+ {
+ if (i > 0)
+ printf(", ");
+ dump_expr(stmt->params[i]);
+ }
+ printf(")");
+ }
+ printf("\n");
+
+ dump_indent += 2;
+ for (i = 0; i < stmt->body->stmts_used; i++)
+ dump_stmt((PLpgSQL_stmt *) (stmt->body->stmts[i]));
+ dump_indent -= 2;
+
+ dump_ind();
+ printf(" ENDFORC\n");
+}
+
+static void
dump_select(PLpgSQL_stmt_select * stmt)
{
dump_ind();
@@ -700,6 +749,59 @@ dump_dynfors(PLpgSQL_stmt_dynfors * stmt

dump_ind();
printf(" ENDFORS\n");
+}
+
+static void
+dump_open(PLpgSQL_stmt_open * stmt)
+{
+ dump_ind();
+ printf("OPEN %s", stmt->cursor->refname);
+ if (stmt->nparams > 0)
+ {
+ int i;
+
+ printf("(");
+ for (i = 0; i < stmt->nparams; ++i)
+ {
+ if (i > 0)
+ printf(", ");
+ dump_expr(stmt->params[i]);
+ }
+ printf(")");
+ }
+ printf("\n");
+}
+
+static void
+dump_fetch(PLpgSQL_stmt_fetch * stmt)
+{
+ printf("FETCH %s INTO", stmt->cursor->refname);
+ if (stmt->nvars == 0)
+ {
+ if (stmt->rec != NULL)
+ printf(" %s", stmt->rec->refname);
+ else
+ printf(" %s", stmt->row->refname);
+ }
+ else
+ {
+ int i;
+
+ for (i = 0; i < stmt->nvars; ++i)
+ {
+ if (i > 0)
+ printf(",");
+ printf(" var %d", stmt->varnos[i]);
+ }
+ }
+ printf("\n");
+}
+
+static void
+dump_close(PLpgSQL_stmt_close * stmt)
+{
+ dump_ind();
+ printf("CLOSE %s\n", stmt->cursor->refname);
}

static void
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.11
diff -p -u -r1.11 plpgsql.h
--- src/pl/plpgsql/src/plpgsql.h 2000/09/05 09:02:18 1.11
+++ src/pl/plpgsql/src/plpgsql.h 2001/01/23 00:07:22
@@ -57,7 +57,8 @@ enum
PLPGSQL_NSTYPE_VAR,
PLPGSQL_NSTYPE_ROW,
PLPGSQL_NSTYPE_REC,
- PLPGSQL_NSTYPE_RECFIELD
+ PLPGSQL_NSTYPE_RECFIELD,
+ PLPGSQL_NSTYPE_CURSOR
};

/* ----------
@@ -71,7 +72,8 @@ enum
PLPGSQL_DTYPE_REC,
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_EXPR,
- PLPGSQL_DTYPE_TRIGARG
+ PLPGSQL_DTYPE_TRIGARG,
+ PLPGSQL_DTYPE_CURSOR
};

/* ----------
@@ -87,6 +89,7 @@ enum
PLPGSQL_STMT_WHILE,
PLPGSQL_STMT_FORI,
PLPGSQL_STMT_FORS,
+ PLPGSQL_STMT_FORC,
PLPGSQL_STMT_SELECT,
PLPGSQL_STMT_EXIT,
PLPGSQL_STMT_RETURN,
@@ -94,6 +97,9 @@ enum
PLPGSQL_STMT_EXECSQL,
PLPGSQL_STMT_DYNEXECUTE,
PLPGSQL_STMT_DYNFORS,
+ PLPGSQL_STMT_OPEN,
+ PLPGSQL_STMT_FETCH,
+ PLPGSQL_STMT_CLOSE,
PLPGSQL_STMT_GETDIAG
};

@@ -227,6 +233,31 @@ typedef struct


typedef struct
+{ /* Cursor */
+ int dtype;
+ int cursorno;
+ char *refname;
+ int lineno;
+
+ PLpgSQL_expr *select;
+ int n_params;
+ int *params;
+
+ int found_varno;
+ int isopen_varno;
+ int notfound_varno;
+ int rowcount_varno;
+ int oid_varno;
+ bool saw_current_of;
+ bool oid_added;
+
+ SPITupleTable *tuptable;
+ int count;
+ int index; /* Index in tuptable; -1 if cursor closed */
+} PLpgSQL_cursor;
+
+
+typedef struct
{ /* Item in the compilers namestack */
int itemtype;
int itemno;
@@ -355,6 +386,20 @@ typedef struct


typedef struct
+{ /* FOR statement running over cursor */
+ int cmd_type;
+ int lineno;
+ char *label;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row;
+ PLpgSQL_cursor *cursor;
+ int nparams;
+ PLpgSQL_expr **params;
+ PLpgSQL_stmts *body;
+} PLpgSQL_stmt_forc;
+
+
+typedef struct
{ /* SELECT ... INTO statement */
int cmd_type;
int lineno;
@@ -410,6 +455,36 @@ typedef struct
} PLpgSQL_stmt_dynexecute;


+typedef struct
+{ /* OPEN statement */
+ int cmd_type;
+ int lineno;
+ PLpgSQL_cursor *cursor;
+ int nparams;
+ PLpgSQL_expr **params;
+} PLpgSQL_stmt_open;
+
+
+typedef struct
+{ /* FETCH statement */
+ int cmd_type;
+ int lineno;
+ PLpgSQL_cursor *cursor;
+ int nvars;
+ int *varnos;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row;
+} PLpgSQL_stmt_fetch;
+
+
+typedef struct
+{ /* CLOSE statement */
+ int cmd_type;
+ int lineno;
+ PLpgSQL_cursor *cursor;
+} PLpgSQL_stmt_close;
+
+
typedef struct PLpgSQL_function
{ /* Complete compiled function */
Oid fn_oid;
@@ -492,6 +567,7 @@ extern int plpgsql_parse_tripword(char *
extern int plpgsql_parse_wordtype(char *string);
extern int plpgsql_parse_dblwordtype(char *string);
extern int plpgsql_parse_wordrowtype(char *string);
+extern int plpgsql_parse_attribute(char *string);
extern void plpgsql_adddatum(PLpgSQL_datum * new);
extern int plpgsql_add_initdatums(int **varnos);
extern void plpgsql_comperrinfo(void);
@@ -548,6 +624,7 @@ extern PLpgSQL_expr *plpgsql_read_expres
extern void plpgsql_yyrestart(FILE *fp);
extern int plpgsql_yylex(void);
extern void plpgsql_setinput(char *s, int functype);
+extern void plpgsql_unput(char);
extern int plpgsql_yyparse(void);
extern void plpgsql_yyerror(const char *s);

Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.8
diff -p -u -r1.8 scan.l
--- src/pl/plpgsql/src/scan.l 2000/09/15 11:59:40 1.8
+++ src/pl/plpgsql/src/scan.l 2001/01/23 00:07:22
@@ -48,7 +48,6 @@ extern int yylineno;
static void plpgsql_input(char *buf, int *result, int max);

#define YY_INPUT(buf,res,max) plpgsql_input(buf, &res, max)
-#define YY_NO_UNPUT
%}

WS [\200-\377_A-Za-z"]
@@ -93,7 +92,9 @@ alias { return K_ALIAS; }
begin { return K_BEGIN; }
bpchar { return T_BPCHAR; }
char { return T_CHAR; }
+close { return K_CLOSE; }
constant { return K_CONSTANT; }
+cursor { return K_CURSOR; }
debug { return K_DEBUG; }
declare { return K_DECLARE; }
default { return K_DEFAULT; }
@@ -103,6 +104,7 @@ end { return K_END; }
exception { return K_EXCEPTION; }
execute { return K_EXECUTE; }
exit { return K_EXIT; }
+fetch { return K_FETCH; }
for { return K_FOR; }
from { return K_FROM; }
get { return K_GET; }
@@ -113,6 +115,7 @@ loop { return K_LOOP; }
not { return K_NOT; }
notice { return K_NOTICE; }
null { return K_NULL; }
+open { return K_OPEN; }
perform { return K_PERFORM; }
processed { return K_PROCESSED; }
raise { return K_RAISE; }
@@ -143,6 +146,10 @@ dump { return O_DUMP; }
{WS}{WC}*%TYPE { return plpgsql_parse_wordtype(yytext); }
{WS}{WC}*\.{WS}{WC}*%TYPE { return plpgsql_parse_dblwordtype(yytext); }
{WS}{WC}*%ROWTYPE { return plpgsql_parse_wordrowtype(yytext); }
+{WS}{WC}*%FOUND { return plpgsql_parse_attribute(yytext); }
+{WS}{WC}*%ISOPEN { return plpgsql_parse_attribute(yytext); }
+{WS}{WC}*%NOTFOUND { return plpgsql_parse_attribute(yytext); }
+{WS}{WC}*%ROWCOUNT { return plpgsql_parse_attribute(yytext); }

\$[0-9]+ { return plpgsql_parse_word(yytext); }
[0-9]+ { return T_NUMBER; }
@@ -249,4 +256,11 @@ plpgsql_setinput(char *source, int funct

scanner_functype = functype;
scanner_typereported = 0;
+}
+
+
+void
+plpgsql_unput(char c)
+{
+ unput(c);
}
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.5
diff -p -u -r1.5 plpgsql.out
--- src/test/regress/expected/plpgsql.out 2000/10/22 23:32:45 1.5
+++ src/test/regress/expected/plpgsql.out 2001/01/23 00:07:23
@@ -1515,3 +1515,97 @@ insert into IFace values ('IF', 'notther
ERROR: system "notthere" does not exist
insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
ERROR: IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max)
+--
+-- Test cursors
+--
+create function test_cursors()
+returns text as '
+declare
+ cursor mycursor(slot char(20)) is
+ select comment from PLine where slotname = slot;
+ crec mycursor%ROWTYPE;
+ srec record;
+ rcount integer;
+ ctext text;
+ cursor allcursor is select * from PLine;
+ allrec allcursor%ROWTYPE;
+ dcount integer;
+begin
+ rcount := 0;
+ for srec in select comment from PLine where slotname = ''PL.001'' loop
+ if rcount != 0 then
+ raise exception ''test_cursors: too many records in for select'';
+ end if;
+ rcount := rcount + 1;
+ if srec.comment != ''Central call'' then
+ raise exception ''test_cursors: bad comment in for select'';
+ end if;
+ end loop;
+ rcount := 0;
+ if mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should not be open'';
+ end if;
+ for crec in mycursor(''PL.001'') loop
+ if rcount != 0 then
+ raise exception ''test_cursors: too many records'';
+ end if;
+ rcount := rcount + 1;
+ if not mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should be open'';
+ end if;
+ if crec.comment != ''Central call'' then
+ raise exception ''test_cursors:: bad comment'';
+ end if;
+ end loop;
+ if mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should not be open'';
+ end if;
+ open mycursor(''PL.001'');
+ if not mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should be open'';
+ end if;
+ fetch mycursor into crec;
+ if not mycursor%FOUND then
+ raise exception ''test_cursors: first record not found 1'';
+ end if;
+ if mycursor%NOTFOUND then
+ raise exception ''test_cursors: first record not found 2'';
+ end if;
+ if crec.comment != ''Central call'' then
+ raise exception ''test_cursors:: bad comment after fetch'';
+ end if;
+ update PLine set comment = ''Central call number''
+ where current of mycursor;
+ fetch mycursor into crec;
+ if mycursor%FOUND then
+ raise exception ''test_cursors: second record found'';
+ end if;
+ if mycursor%ROWCOUNT != 1 then
+ raise exception ''test_cursors: bad row count'';
+ end if;
+ close mycursor;
+ if mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor open after close'';
+ end if;
+ select into ctext comment from PLine where slotname = ''PL.001'';
+ if ctext != ''Central call number'' then
+ raise exception ''test_cursors: modification failed'';
+ end if;
+ rcount := 0;
+ for allrec in allcursor loop
+ rcount := rcount + 1;
+ end loop;
+ select into dcount count(*) from Pline;
+ if rcount != dcount then
+ raise exception ''test_cursors: count mismatch % != %'',
+ rcount, dcount;
+ end if;
+ return ''ok'';
+end;
+' language 'plpgsql';
+select test_cursors();
+ test_cursors
+--------------
+ ok
+(1 row)
+
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.4
diff -p -u -r1.4 plpgsql.sql
--- src/test/regress/sql/plpgsql.sql 2000/10/22 23:32:46 1.4
+++ src/test/regress/sql/plpgsql.sql 2001/01/23 00:07:23
@@ -1399,3 +1399,106 @@ delete from HSlot;
insert into IFace values ('IF', 'notthere', 'eth0', '');
insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');

+--
+-- Test cursors
+--
+
+create function test_cursors()
+returns text as '
+declare
+ cursor mycursor(slot char(20)) is
+ select comment from PLine where slotname = slot;
+ crec mycursor%ROWTYPE;
+ srec record;
+ rcount integer;
+ ctext text;
+ cursor allcursor is select * from PLine;
+ allrec allcursor%ROWTYPE;
+ dcount integer;
+begin
+ rcount := 0;
+ for srec in select comment from PLine where slotname = ''PL.001'' loop
+ if rcount != 0 then
+ raise exception ''test_cursors: too many records in for select'';
+ end if;
+ rcount := rcount + 1;
+ if srec.comment != ''Central call'' then
+ raise exception ''test_cursors: bad comment in for select'';
+ end if;
+ end loop;
+
+ rcount := 0;
+ if mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should not be open'';
+ end if;
+ for crec in mycursor(''PL.001'') loop
+ if rcount != 0 then
+ raise exception ''test_cursors: too many records'';
+ end if;
+ rcount := rcount + 1;
+
+ if not mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should be open'';
+ end if;
+
+ if crec.comment != ''Central call'' then
+ raise exception ''test_cursors:: bad comment'';
+ end if;
+ end loop;
+
+ if mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should not be open'';
+ end if;
+ open mycursor(''PL.001'');
+ if not mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor should be open'';
+ end if;
+
+ fetch mycursor into crec;
+ if not mycursor%FOUND then
+ raise exception ''test_cursors: first record not found 1'';
+ end if;
+ if mycursor%NOTFOUND then
+ raise exception ''test_cursors: first record not found 2'';
+ end if;
+
+ if crec.comment != ''Central call'' then
+ raise exception ''test_cursors:: bad comment after fetch'';
+ end if;
+
+ update PLine set comment = ''Central call number''
+ where current of mycursor;
+
+ fetch mycursor into crec;
+ if mycursor%FOUND then
+ raise exception ''test_cursors: second record found'';
+ end if;
+ if mycursor%ROWCOUNT != 1 then
+ raise exception ''test_cursors: bad row count'';
+ end if;
+
+ close mycursor;
+ if mycursor%ISOPEN then
+ raise exception ''test_cursors: cursor open after close'';
+ end if;
+
+ select into ctext comment from PLine where slotname = ''PL.001'';
+ if ctext != ''Central call number'' then
+ raise exception ''test_cursors: modification failed'';
+ end if;
+
+ rcount := 0;
+ for allrec in allcursor loop
+ rcount := rcount + 1;
+ end loop;
+ select into dcount count(*) from Pline;
+ if rcount != dcount then
+ raise exception ''test_cursors: count mismatch % != %'',
+ rcount, dcount;
+ end if;
+
+ return ''ok'';
+end;
+' language 'plpgsql';
+
+select test_cursors();