*** ./doc/src/sgml/plpgsql.sgml.orig 2007-11-30 15:22:30.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml 2007-11-30 16:17:51.000000000 +0100
***************
*** 2417,2422 ****
--- 2417,2423 ----
+
UPDATE/DELETE WHERE CURRENT OF>
***************
*** 2574,2579 ****
--- 2575,2601 ----
+
+
+ Looping Through Cursor
+
+
+ Using of a FOR loop, you can simple iterate through cursor. The syntax is:
+
+ <<label>>
+ FOR recordvar IN bound_cursor ( argument_values ) LOOP
+ statements
+ END LOOP label ;
+
+ Cursor have to be bound to some query and cursor cannot be opened already. A list of
+ actual argument value expressions must appear if and only if the cursor was declared
+ to take arguments. These values will be substituted in the query. When control leaves
+ statement, the cursor is closed. The variable recordvar is
+ automatically defined as type record> and exists only inside the loop (any existing
+ definition of the variable name is ignored within the loop).
+
+
+
*** ./src/pl/plpgsql/src/gram.y.orig 2007-11-27 20:58:44.000000000 +0100
--- ./src/pl/plpgsql/src/gram.y 2007-11-30 14:39:48.000000000 +0100
***************
*** 46,51 ****
--- 46,53 ----
static char *check_label(const char *yytxt);
static void check_labels(const char *start_label,
const char *end_label);
+ static PLpgSQL_expr *read_cursor_args(int until,
+ const char * expected);
%}
***************
*** 865,870 ****
--- 867,881 ----
new->body = $4.stmts;
$$ = (PLpgSQL_stmt *) new;
}
+ else if ($3->cmd_type == PLPGSQL_STMT_FORC)
+ {
+ PLpgSQL_stmt_forc *new;
+
+ new = (PLpgSQL_stmt_forc *) $3;
+ new->label = $1;
+ new->body = $4.stmts;
+ $$ = (PLpgSQL_stmt *) new;
+ }
else
{
PLpgSQL_stmt_dynfors *new;
***************
*** 927,933 ****
{
PLpgSQL_expr *expr1;
bool reverse = false;
!
/*
* We have to distinguish between two
* alternatives: FOR var IN a .. b and FOR
--- 938,944 ----
{
PLpgSQL_expr *expr1;
bool reverse = false;
!
/*
* We have to distinguish between two
* alternatives: FOR var IN a .. b and FOR
***************
*** 940,1066 ****
* keyword, which means it must be an
* integer loop.
*/
- if (tok == K_REVERSE)
- reverse = true;
- else
- plpgsql_push_back_token(tok);
! /*
! * Read tokens until we see either a ".."
! * or a LOOP. The text we read may not
! * necessarily be a well-formed SQL
! * statement, so we need to invoke
! * read_sql_construct directly.
*/
- expr1 = read_sql_construct(K_DOTDOT,
- K_LOOP,
- "LOOP",
- "SELECT ",
- true,
- false,
- &tok);
! if (tok == K_DOTDOT)
{
- /* Saw "..", so it must be an integer loop */
- PLpgSQL_expr *expr2;
- PLpgSQL_expr *expr_by;
- PLpgSQL_var *fvar;
- PLpgSQL_stmt_fori *new;
char *varname;
! /* Check first expression is well-formed */
! check_sql_expr(expr1->query);
! /* Read and check the second one */
! expr2 = read_sql_construct(K_LOOP,
! K_BY,
! "LOOP",
! "SELECT ",
! true,
! true,
! &tok);
!
! /* Get the BY clause if any */
! if (tok == K_BY)
! expr_by = plpgsql_read_expression(K_LOOP, "LOOP");
! else
! expr_by = NULL;
/* Should have had a single variable name */
plpgsql_error_lineno = $2.lineno;
if ($2.scalar && $2.row)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("integer FOR loop must have just one target variable")));
! /* create loop's private variable */
plpgsql_convert_ident($2.name, &varname, 1);
! fvar = (PLpgSQL_var *)
! plpgsql_build_variable(varname,
! $2.lineno,
! plpgsql_build_datatype(INT4OID,
! -1),
! true);
!
! new = palloc0(sizeof(PLpgSQL_stmt_fori));
! new->cmd_type = PLPGSQL_STMT_FORI;
! new->lineno = $1;
! new->var = fvar;
! new->reverse = reverse;
! new->lower = expr1;
! new->upper = expr2;
! new->step = expr_by;
$$ = (PLpgSQL_stmt *) new;
}
else
{
/*
! * No "..", so it must be a query loop. We've prefixed an
! * extra SELECT to the query text, so we need to remove that
! * before performing syntax checking.
*/
! char *tmp_query;
! PLpgSQL_stmt_fors *new;
!
! if (reverse)
! yyerror("cannot specify REVERSE in query FOR loop");
! Assert(strncmp(expr1->query, "SELECT ", 7) == 0);
! tmp_query = pstrdup(expr1->query + 7);
! pfree(expr1->query);
! expr1->query = tmp_query;
!
! check_sql_expr(expr1->query);
!
! new = palloc0(sizeof(PLpgSQL_stmt_fors));
! new->cmd_type = PLPGSQL_STMT_FORS;
! new->lineno = $1;
! if ($2.rec)
! {
! new->rec = $2.rec;
! check_assignable((PLpgSQL_datum *) new->rec);
! }
! else if ($2.row)
{
! new->row = $2.row;
! check_assignable((PLpgSQL_datum *) new->row);
! }
! else if ($2.scalar)
! {
! /* convert single scalar to list */
! new->row = make_scalar_list1($2.name, $2.scalar, $2.lineno);
! /* no need for check_assignable */
}
else
{
! plpgsql_error_lineno = $2.lineno;
! yyerror("loop variable of loop over rows must be a record or row variable or list of scalar variables");
! }
! new->query = expr1;
! $$ = (PLpgSQL_stmt *) new;
}
}
}
--- 951,1159 ----
* keyword, which means it must be an
* integer loop.
*/
! /*
! * If token is scalar and it's cursor variable,
! * then choise third alternative FOR recvar IN cursor
*/
! if (tok == T_SCALAR
! && yylval.scalar->dtype == PLPGSQL_DTYPE_VAR
! && ((PLpgSQL_var *) yylval.scalar)->datatype->typoid == REFCURSOROID)
{
char *varname;
+ PLpgSQL_stmt_forc *new;
+ PLpgSQL_var *cursor = (PLpgSQL_var *) yylval.scalar;
! new = (PLpgSQL_stmt_forc *) palloc0(sizeof(PLpgSQL_stmt_forc));
! new->cmd_type = PLPGSQL_STMT_FORC;
! new->lineno = $1;
! new->curvar = cursor->varno;
/* Should have had a single variable name */
plpgsql_error_lineno = $2.lineno;
if ($2.scalar && $2.row)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cursor FOR loop must have just one target variable")));
! /* create loop's private RECORD variable */
plpgsql_convert_ident($2.name, &varname, 1);
!
! new->rec = plpgsql_build_record(varname, $2.lineno);
!
! /*
! * take parameters
! */
! if (cursor->cursor_explicit_argrow > 0)
! {
! tok = yylex();
! if (tok != '(')
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cursor \"%s\" has arguments",
! cursor->refname)));
! }
!
! /*
! * Push back the '(', else read_sql_stmt
! * will complain about unbalanced parens.
! */
! plpgsql_push_back_token(tok);
!
! new->argquery = read_cursor_args(K_LOOP, "LOOP");
! }
! else
! {
! tok = yylex();
! if (tok == '(')
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cursor \"%s\" has no arguments",
! cursor->refname)));
! }
!
! if (tok != K_LOOP)
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("syntax error at \"%s\"",
! yytext)));
! }
! }
$$ = (PLpgSQL_stmt *) new;
}
else
{
+ if (tok == K_REVERSE)
+ reverse = true;
+ else
+ plpgsql_push_back_token(tok);
+
/*
! * Read tokens until we see either a ".."
! * or a LOOP. The text we read may not
! * necessarily be a well-formed SQL
! * statement, so we need to invoke
! * read_sql_construct directly.
*/
! expr1 = read_sql_construct(K_DOTDOT,
! K_LOOP,
! "LOOP",
! "SELECT ",
! true,
! false,
! &tok);
! if (tok == K_DOTDOT)
{
! /* Saw "..", so it must be an integer loop */
! PLpgSQL_expr *expr2;
! PLpgSQL_expr *expr_by;
! PLpgSQL_var *fvar;
! PLpgSQL_stmt_fori *new;
! char *varname;
!
! /* Check first expression is well-formed */
! check_sql_expr(expr1->query);
!
! /* Read and check the second one */
! expr2 = read_sql_construct(K_LOOP,
! K_BY,
! "LOOP",
! "SELECT ",
! true,
! true,
! &tok);
!
! /* Get the BY clause if any */
! if (tok == K_BY)
! expr_by = plpgsql_read_expression(K_LOOP, "LOOP");
! else
! expr_by = NULL;
!
! /* Should have had a single variable name */
! plpgsql_error_lineno = $2.lineno;
! if ($2.scalar && $2.row)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("integer FOR loop must have just one target variable")));
!
! /* create loop's private variable */
! plpgsql_convert_ident($2.name, &varname, 1);
! fvar = (PLpgSQL_var *)
! plpgsql_build_variable(varname,
! $2.lineno,
! plpgsql_build_datatype(INT4OID,
! -1),
! true);
!
! new = palloc0(sizeof(PLpgSQL_stmt_fori));
! new->cmd_type = PLPGSQL_STMT_FORI;
! new->lineno = $1;
! new->var = fvar;
! new->reverse = reverse;
! new->lower = expr1;
! new->upper = expr2;
! new->step = expr_by;
!
! $$ = (PLpgSQL_stmt *) new;
}
else
{
! /*
! * No "..", so it must be a query loop. We've prefixed an
! * extra SELECT to the query text, so we need to remove that
! * before performing syntax checking.
! */
! char *tmp_query;
! PLpgSQL_stmt_fors *new;
!
! if (reverse)
! yyerror("cannot specify REVERSE in query FOR loop");
!
! Assert(strncmp(expr1->query, "SELECT ", 7) == 0);
! tmp_query = pstrdup(expr1->query + 7);
! pfree(expr1->query);
! expr1->query = tmp_query;
!
! check_sql_expr(expr1->query);
!
! new = palloc0(sizeof(PLpgSQL_stmt_fors));
! new->cmd_type = PLPGSQL_STMT_FORS;
! new->lineno = $1;
! if ($2.rec)
! {
! new->rec = $2.rec;
! check_assignable((PLpgSQL_datum *) new->rec);
! }
! else if ($2.row)
! {
! new->row = $2.row;
! check_assignable((PLpgSQL_datum *) new->row);
! }
! else if ($2.scalar)
! {
! /* convert single scalar to list */
! new->row = make_scalar_list1($2.name, $2.scalar, $2.lineno);
! /* no need for check_assignable */
! }
! else
! {
! plpgsql_error_lineno = $2.lineno;
! yyerror("loop variable of loop over rows must be a record or row variable or list of scalar variables");
! }
! new->query = expr1;
! $$ = (PLpgSQL_stmt *) new;
! }
}
}
}
***************
*** 1385,1392 ****
{
if ($3->cursor_explicit_argrow >= 0)
{
- char *cp;
-
tok = yylex();
if (tok != '(')
{
--- 1478,1483 ----
***************
*** 1403,1441 ****
*/
plpgsql_push_back_token(tok);
! new->argquery = read_sql_stmt("SELECT ");
!
! /*
! * Now remove the leading and trailing parens,
! * because we want "select 1, 2", not
! * "select (1, 2)".
! */
! cp = new->argquery->query;
!
! if (strncmp(cp, "SELECT", 6) != 0)
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! /* internal error */
! elog(ERROR, "expected \"SELECT (\", got \"%s\"",
! new->argquery->query);
! }
! cp += 6;
! while (*cp == ' ') /* could be more than 1 space here */
! cp++;
! if (*cp != '(')
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! /* internal error */
! elog(ERROR, "expected \"SELECT (\", got \"%s\"",
! new->argquery->query);
! }
! *cp = ' ';
!
! cp += strlen(cp) - 1;
!
! if (*cp != ')')
! yyerror("expected \")\"");
! *cp = '\0';
}
else
{
--- 1494,1500 ----
*/
plpgsql_push_back_token(tok);
! new->argquery = read_cursor_args(';',";");
}
else
{
***************
*** 2529,2534 ****
--- 2588,2634 ----
}
}
+
+ /*
+ * returns query without leading and trailing parens
+ *
+ */
+ static PLpgSQL_expr *
+ read_cursor_args(int until, const char * expected)
+ {
+ PLpgSQL_expr *expr;
+ char *cp;
+
+ expr = read_sql_construct(until,
+ 0,
+ expected,
+ "SELECT ",
+ true,
+ false,
+ NULL);
+
+ /*
+ * Now remove the leading and trailing parens,
+ * because we want "select 1, 2", not
+ * "select (1, 2)".
+ */
+ cp = expr->query;
+
+ Assert(strncmp(cp, "SELECT", 6) == 0);
+ cp += 6;
+ while (*cp == ' ') /* could be more than 1 space here */
+ cp++;
+ Assert(*cp == '(');
+ *cp = ' ';
+
+ cp += strlen(cp) - 1;
+
+ Assert(*cp == ')');
+ *cp = '\0';
+
+ return expr;
+ }
+
/* Needed to avoid conflict between different prefix settings: */
#undef yylex
*** ./src/pl/plpgsql/src/pl_comp.c.orig 2007-11-27 20:58:44.000000000 +0100
--- ./src/pl/plpgsql/src/pl_comp.c 2007-11-28 20:23:03.000000000 +0100
***************
*** 574,598 ****
errhint("You probably want to use TG_NARGS and TG_ARGV instead.")));
/* Add the record for referencing NEW */
! rec = palloc0(sizeof(PLpgSQL_rec));
! rec->dtype = PLPGSQL_DTYPE_REC;
! rec->refname = pstrdup("new");
! rec->tup = NULL;
! rec->tupdesc = NULL;
! rec->freetup = false;
! plpgsql_adddatum((PLpgSQL_datum *) rec);
! plpgsql_ns_additem(PLPGSQL_NSTYPE_REC, rec->recno, rec->refname);
function->new_varno = rec->recno;
/* Add the record for referencing OLD */
! rec = palloc0(sizeof(PLpgSQL_rec));
! rec->dtype = PLPGSQL_DTYPE_REC;
! rec->refname = pstrdup("old");
! rec->tup = NULL;
! rec->tupdesc = NULL;
! rec->freetup = false;
! plpgsql_adddatum((PLpgSQL_datum *) rec);
! plpgsql_ns_additem(PLPGSQL_NSTYPE_REC, rec->recno, rec->refname);
function->old_varno = rec->recno;
/* Add the variable tg_name */
--- 574,584 ----
errhint("You probably want to use TG_NARGS and TG_ARGV instead.")));
/* Add the record for referencing NEW */
! rec = plpgsql_build_record("new", 0);
function->new_varno = rec->recno;
/* Add the record for referencing OLD */
! rec = plpgsql_build_record("old", 0);
function->old_varno = rec->recno;
/* Add the variable tg_name */
***************
*** 1515,1520 ****
--- 1501,1529 ----
}
/*
+ * Build empty named record, and add it to namespace
+ *
+ */
+ PLpgSQL_rec *
+ plpgsql_build_record(const char *refname, int lineno)
+ {
+ PLpgSQL_rec *rec;
+
+ rec = palloc0(sizeof(PLpgSQL_rec));
+ rec->dtype = PLPGSQL_DTYPE_REC;
+ rec->refname = pstrdup(refname);
+ rec->lineno = lineno;
+ rec->tup = NULL;
+ rec->tupdesc = NULL;
+ rec->freetup = false;
+ plpgsql_adddatum((PLpgSQL_datum *) rec);
+ plpgsql_ns_additem(PLPGSQL_NSTYPE_REC, rec->recno, rec->refname);
+
+ return rec;
+ }
+
+
+ /*
* Build a row-variable data structure given the pg_class OID.
*/
static PLpgSQL_row *
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2007-11-15 23:25:17.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2007-11-30 13:57:09.000000000 +0100
***************
*** 93,98 ****
--- 93,100 ----
PLpgSQL_stmt_fori *stmt);
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_open(PLpgSQL_execstate *estate,
PLpgSQL_stmt_open *stmt);
static int exec_stmt_fetch(PLpgSQL_execstate *estate,
***************
*** 177,182 ****
--- 179,190 ----
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
static void plpgsql_create_econtext(PLpgSQL_execstate *estate);
static void free_var(PLpgSQL_var *var);
+ static int exec_for_query(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_forq *stmt, Portal portal);
+ static void eval_expr_params(PLpgSQL_execstate *estate,
+ PLpgSQL_expr *query,
+ Datum **values, char **nulls);
+ static void SetTextVar(PLpgSQL_var *var, const char *str);
/* ----------
***************
*** 1234,1239 ****
--- 1242,1251 ----
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_EXIT:
rc = exec_stmt_exit(estate, (PLpgSQL_stmt_exit *) stmt);
break;
***************
*** 1712,1861 ****
static int
exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
{
- PLpgSQL_rec *rec = NULL;
- PLpgSQL_row *row = NULL;
- SPITupleTable *tuptab;
Portal portal;
! bool found = false;
! int rc = PLPGSQL_RC_OK;
! int i;
! int n;
!
! /*
! * 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");
/*
* Open the implicit cursor for the statement and fetch the initial 10
* rows.
*/
exec_run_select(estate, stmt->query, 0, &portal);
!
! SPI_cursor_fetch(portal, true, 10);
! tuptab = SPI_tuptable;
! n = SPI_processed;
/*
! * If the query didn't return any rows, set the target to NULL and return
! * with FOUND = false.
*/
! if (n == 0)
! exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
! else
! found = true; /* processed at least one tuple */
! /*
! * Now do the loop
*/
! while (n > 0)
{
! for (i = 0; i < n; i++)
! {
! /*
! * Assign the tuple to the target
! */
! exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc);
! /*
! * Execute the statements
! */
! rc = exec_stmts(estate, stmt->body);
! if (rc != PLPGSQL_RC_OK)
! {
! if (rc == PLPGSQL_RC_EXIT)
! {
! if (estate->exitlabel == NULL)
! /* unlabelled exit, finish the current loop */
! rc = PLPGSQL_RC_OK;
! else if (stmt->label != NULL &&
! strcmp(stmt->label, estate->exitlabel) == 0)
! {
! /* labelled exit, matches the current stmt's label */
! estate->exitlabel = NULL;
! rc = PLPGSQL_RC_OK;
! }
! /*
! * otherwise, we processed a labelled exit that does not
! * match the current statement's label, if any: return
! * RC_EXIT so that the EXIT continues to recurse upward.
! */
! }
! else if (rc == PLPGSQL_RC_CONTINUE)
! {
! if (estate->exitlabel == NULL)
! {
! /* anonymous continue, so re-run the current loop */
! rc = PLPGSQL_RC_OK;
! continue;
! }
! else if (stmt->label != NULL &&
! strcmp(stmt->label, estate->exitlabel) == 0)
! {
! /* label matches named continue, so re-run loop */
! rc = PLPGSQL_RC_OK;
! estate->exitlabel = NULL;
! continue;
! }
! /*
! * otherwise, we processed a named continue that does not
! * match the current statement's label, if any: return
! * RC_CONTINUE so that the CONTINUE will propagate up the
! * stack.
! */
! }
! /*
! * We're aborting the loop, so cleanup and set FOUND. (This
! * code should match the code after the loop.)
! */
! SPI_freetuptable(tuptab);
! SPI_cursor_close(portal);
! exec_set_found(estate, found);
! return rc;
! }
! }
! SPI_freetuptable(tuptab);
! /*
! * Fetch the next 50 tuples
! */
! SPI_cursor_fetch(portal, true, 50);
! n = SPI_processed;
! tuptab = SPI_tuptable;
! }
! /*
! * Release last group of tuples
*/
! SPI_freetuptable(tuptab);
! /*
! * Close the implicit cursor
*/
! SPI_cursor_close(portal);
! /*
! * Set the FOUND variable to indicate the result of executing the loop
! * (namely, whether we looped one or more times). This must be set here so
! * that it does not interfere with the value of the FOUND variable inside
! * the loop processing itself.
*/
! exec_set_found(estate, found);
return rc;
}
/* ----------
* exec_stmt_exit Implements EXIT and CONTINUE
*
--- 1724,1875 ----
static int
exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
{
Portal portal;
! int rc;
/*
* Open the implicit cursor for the statement and fetch the initial 10
* rows.
*/
exec_run_select(estate, stmt->query, 0, &portal);
! rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal);
/*
! * Close the implicit cursor
*/
! SPI_cursor_close(portal);
! return rc;
! }
!
!
! /* ----------
! * exec_stmt_forc Open cursor and fetch all rows
! * to record variable.
! *
! *
! * ----------
! */
! static int
! exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt)
! {
! PLpgSQL_var *curvar = NULL;
! char *curname = NULL;
! PLpgSQL_expr *query = NULL;
! Portal portal;
! int rc;
! Datum *values;
! char *nulls;
!
!
! /* ----------
! * Get the cursor variable and if it has an assigned name, check
! * that it's not in use currently.
! * ----------
*/
! curvar = (PLpgSQL_var *) (estate->datums[stmt->curvar]);
! if (!curvar->isnull)
{
! curname = DatumGetCString(DirectFunctionCall1(textout, curvar->value));
! if (SPI_cursor_find(curname) != NULL)
! ereport(ERROR,
! (errcode(ERRCODE_DUPLICATE_CURSOR),
! errmsg("cursor \"%s\" already in use", curname)));
! }
! curname = DatumGetCString(DirectFunctionCall1(textout, curvar->value));
! /* ----------
! * This is an OPEN cursor
! *
! * Note: parser should already have checked that statement supplies
! * args iff cursor needs them, but we check again to be safe.
! * ----------
! */
! if (stmt->argquery != NULL)
! {
! /* ----------
! * OPEN CURSOR with args. We fake a SELECT ... INTO ...
! * statement to evaluate the args and put 'em into the
! * internal row.
! * ----------
! */
! PLpgSQL_stmt_execsql set_args;
! if (curvar->cursor_explicit_argrow < 0)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("arguments given for cursor without arguments")));
! memset(&set_args, 0, sizeof(set_args));
! set_args.cmd_type = PLPGSQL_STMT_EXECSQL;
! set_args.lineno = stmt->lineno;
! set_args.sqlstmt = stmt->argquery;
! set_args.into = true;
! /* XXX historically this has not been STRICT */
! set_args.row = (PLpgSQL_row *)
! (estate->datums[curvar->cursor_explicit_argrow]);
! if (exec_stmt_execsql(estate, &set_args) != PLPGSQL_RC_OK)
! elog(ERROR, "open cursor failed during argument processing");
! }
! else
! {
! if (curvar->cursor_explicit_argrow >= 0)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("arguments required for cursor")));
! }
! query = curvar->cursor_explicit_expr;
! if (query->plan == NULL)
! exec_prepare_plan(estate, query, curvar->cursor_options);
!
! /* ----------
! * Here we go if we have a saved plan where we have to put
! * values into, either from an explicit cursor or from a
! * refcursor opened with OPEN ... FOR SELECT ...;
! * ----------
! */
! eval_expr_params(estate, query, &values, &nulls);
! /* ----------
! * Open the cursor
! * ----------
*/
! portal = SPI_cursor_open(curname, query->plan, values, nulls,
! estate->readonly_func);
! if (portal == NULL)
! elog(ERROR, "could not open cursor: %s",
! SPI_result_code_string(SPI_result));
! pfree(values);
! pfree(nulls);
! if (curname)
! pfree(curname);
!
!
! /* ----------
! * Store the eventually assigned portal name in the cursor variable
! * ----------
*/
! SetTextVar(curvar, portal->name);
! rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal);
!
! /* ----------
! * Close portal and clean cursor variable.
! * ----------
*/
! SPI_cursor_close(portal);
! free_var(curvar);
return rc;
}
+
/* ----------
* exec_stmt_exit Implements EXIT and CONTINUE
*
***************
*** 2459,2465 ****
exec_stmt_execsql(PLpgSQL_execstate *estate,
PLpgSQL_stmt_execsql *stmt)
{
- int i;
Datum *values;
char *nulls;
long tcount;
--- 2473,2478 ----
***************
*** 2500,2521 ****
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
*/
! values = (Datum *) palloc(expr->nparams * sizeof(Datum));
! nulls = (char *) palloc(expr->nparams * sizeof(char));
!
! for (i = 0; i < expr->nparams; i++)
! {
! PLpgSQL_datum *datum = estate->datums[expr->params[i]];
! Oid paramtypeid;
! bool paramisnull;
!
! exec_eval_datum(estate, datum, expr->plan_argtypes[i],
! ¶mtypeid, &values[i], ¶misnull);
! if (paramisnull)
! nulls[i] = 'n';
! else
! nulls[i] = ' ';
! }
/*
* If we have INTO, then we only need one row back ... but if we have INTO
--- 2513,2519 ----
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
*/
! eval_expr_params(estate, expr, &values, &nulls);
/*
* If we have INTO, then we only need one row back ... but if we have INTO
***************
*** 2806,2995 ****
pfree(querystr);
return PLPGSQL_RC_OK;
! }
!
!
! /* ----------
! * exec_stmt_dynfors Execute a dynamic query, assign each
! * tuple to a record or row and
! * execute a group of statements
! * for it.
! * ----------
! */
! static int
! exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt)
! {
! Datum query;
! bool isnull;
! Oid restype;
! char *querystr;
! PLpgSQL_rec *rec = NULL;
! PLpgSQL_row *row = NULL;
! SPITupleTable *tuptab;
! int n;
! SPIPlanPtr plan;
! Portal portal;
! bool found = 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");
!
! /*
! * Evaluate the string expression after the EXECUTE keyword. It's result
! * is the querystring we have to execute.
! */
! query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
! if (isnull)
! ereport(ERROR,
! (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
! errmsg("cannot EXECUTE a null querystring")));
!
! /* Get the C-String representation */
! querystr = convert_value_to_string(query, restype);
!
! exec_eval_cleanup(estate);
!
! /*
! * Prepare a plan and open an implicit cursor for the query
! */
! plan = SPI_prepare(querystr, 0, NULL);
! if (plan == NULL)
! elog(ERROR, "SPI_prepare failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! portal = SPI_cursor_open(NULL, plan, NULL, NULL,
! estate->readonly_func);
! if (portal == NULL)
! elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! pfree(querystr);
! SPI_freeplan(plan);
!
! /*
! * Fetch the initial 10 tuples
! */
! SPI_cursor_fetch(portal, true, 10);
! tuptab = SPI_tuptable;
! n = SPI_processed;
!
! /*
! * If the query didn't return any rows, set the target to NULL and return
! * with FOUND = false.
! */
! if (n == 0)
! exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
! else
! found = true; /* processed at least one tuple */
!
! /*
! * Now do the loop
! */
! while (n > 0)
! {
! int i;
!
! for (i = 0; i < n; i++)
! {
! int rc;
!
! /*
! * Assign the tuple to the target
! */
! exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc);
!
! /*
! * Execute the statements
! */
! rc = exec_stmts(estate, stmt->body);
!
! if (rc != PLPGSQL_RC_OK)
! {
! if (rc == PLPGSQL_RC_EXIT)
! {
! if (estate->exitlabel == NULL)
! /* unlabelled exit, finish the current loop */
! rc = PLPGSQL_RC_OK;
! else if (stmt->label != NULL &&
! strcmp(stmt->label, estate->exitlabel) == 0)
! {
! /* labelled exit, matches the current stmt's label */
! estate->exitlabel = NULL;
! rc = PLPGSQL_RC_OK;
! }
!
! /*
! * otherwise, we processed a labelled exit that does not
! * match the current statement's label, if any: return
! * RC_EXIT so that the EXIT continues to recurse upward.
! */
! }
! else if (rc == PLPGSQL_RC_CONTINUE)
! {
! if (estate->exitlabel == NULL)
! /* unlabelled continue, continue the current loop */
! continue;
! else if (stmt->label != NULL &&
! strcmp(stmt->label, estate->exitlabel) == 0)
! {
! /* labelled continue, matches the current stmt's label */
! estate->exitlabel = NULL;
! continue;
! }
!
! /*
! * otherwise, we process a labelled continue that does not
! * match the current statement's label, so propagate
! * RC_CONTINUE upward in the stack.
! */
! }
!
! /*
! * We're aborting the loop, so cleanup and set FOUND. (This
! * code should match the code after the loop.)
! */
! SPI_freetuptable(tuptab);
! SPI_cursor_close(portal);
! exec_set_found(estate, found);
- return rc;
- }
- }
! SPI_freetuptable(tuptab);
- /*
- * Fetch the next 50 tuples
- */
- SPI_cursor_fetch(portal, true, 50);
- n = SPI_processed;
- tuptab = SPI_tuptable;
- }
/*
! * Release last group of tuples
*/
! SPI_freetuptable(tuptab);
/*
! * Close the implicit cursor
*/
! SPI_cursor_close(portal);
/*
! * Set the FOUND variable to indicate the result of executing the loop
! * (namely, whether we looped one or more times). This must be set here so
! * that it does not interfere with the value of the FOUND variable inside
! * the loop processing itself.
*/
! exec_set_found(estate, found);
! return PLPGSQL_RC_OK;
}
--- 2804,2869 ----
pfree(querystr);
return PLPGSQL_RC_OK;
! }
! /* ----------
! * exec_stmt_dynfors Execute a dynamic query, assign each
! * tuple to a record or row and
! * execute a group of statements
! * for it.
! * ----------
! */
! static int
! exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt)
! {
! Datum query;
! bool isnull;
! Oid restype;
! char *querystr;
! int rc;
! SPIPlanPtr plan;
! Portal portal;
/*
! * Evaluate the string expression after the EXECUTE keyword. It's result
! * is the querystring we have to execute.
*/
! query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
! if (isnull)
! ereport(ERROR,
! (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
! errmsg("cannot EXECUTE a null querystring")));
!
! /* Get the C-String representation */
! querystr = convert_value_to_string(query, restype);
!
! exec_eval_cleanup(estate);
/*
! * Prepare a plan and open an implicit cursor for the query
*/
! plan = SPI_prepare(querystr, 0, NULL);
! if (plan == NULL)
! elog(ERROR, "SPI_prepare failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! portal = SPI_cursor_open(NULL, plan, NULL, NULL,
! estate->readonly_func);
! if (portal == NULL)
! elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! pfree(querystr);
! SPI_freeplan(plan);
!
! rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal);
/*
! * Close the implicit cursor
*/
! SPI_cursor_close(portal);
! return rc;
}
***************
*** 3004,3010 ****
char *curname = NULL;
PLpgSQL_expr *query = NULL;
Portal portal;
- int i;
Datum *values;
char *nulls;
bool isnull;
--- 2878,2883 ----
***************
*** 3090,3099 ****
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! free_var(curvar);
! curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
! curvar->isnull = false;
! curvar->freeval = true;
return PLPGSQL_RC_OK;
}
--- 2963,2969 ----
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! SetTextVar(curvar, portal->name);
return PLPGSQL_RC_OK;
}
***************
*** 3152,3173 ****
* refcursor opened with OPEN ... FOR SELECT ...;
* ----------
*/
- values = (Datum *) palloc(query->nparams * sizeof(Datum));
- nulls = (char *) palloc(query->nparams * sizeof(char));
-
- for (i = 0; i < query->nparams; i++)
- {
- PLpgSQL_datum *datum = estate->datums[query->params[i]];
- Oid paramtypeid;
- bool paramisnull;
! exec_eval_datum(estate, datum, query->plan_argtypes[i],
! ¶mtypeid, &values[i], ¶misnull);
! if (paramisnull)
! nulls[i] = 'n';
! else
! nulls[i] = ' ';
! }
/* ----------
* Open the cursor
--- 3022,3029 ----
* refcursor opened with OPEN ... FOR SELECT ...;
* ----------
*/
! eval_expr_params(estate, query, &values, &nulls);
/* ----------
* Open the cursor
***************
*** 3188,3197 ****
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! free_var(curvar);
! curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
! curvar->isnull = false;
! curvar->freeval = true;
return PLPGSQL_RC_OK;
}
--- 3044,3050 ----
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! SetTextVar(curvar, portal->name);
return PLPGSQL_RC_OK;
}
***************
*** 4044,4050 ****
exec_run_select(PLpgSQL_execstate *estate,
PLpgSQL_expr *expr, long maxtuples, Portal *portalP)
{
- int i;
Datum *values;
char *nulls;
int rc;
--- 3897,3902 ----
***************
*** 4058,4079 ****
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
*/
! values = (Datum *) palloc(expr->nparams * sizeof(Datum));
! nulls = (char *) palloc(expr->nparams * sizeof(char));
!
! for (i = 0; i < expr->nparams; i++)
! {
! PLpgSQL_datum *datum = estate->datums[expr->params[i]];
! Oid paramtypeid;
! bool paramisnull;
!
! exec_eval_datum(estate, datum, expr->plan_argtypes[i],
! ¶mtypeid, &values[i], ¶misnull);
! if (paramisnull)
! nulls[i] = 'n';
! else
! nulls[i] = ' ';
! }
/*
* If a portal was requested, put the query into the portal
--- 3910,3916 ----
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
*/
! eval_expr_params(estate, expr, &values, &nulls);
/*
* If a portal was requested, put the query into the portal
***************
*** 5070,5072 ****
--- 4907,5089 ----
var->freeval = false;
}
}
+
+ /*
+ * exec_for_query --- evaluate body of for statement on every portal's row
+ *
+ * Used in exec_stmt_fors, exec_stmt_forc and exec_stmt_dynfors
+ */
+ static int
+ exec_for_query(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_forq *stmt, Portal portal)
+ {
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
+ SPITupleTable *tuptab;
+ bool found = false;
+ int n;
+
+ /*
+ * Fetch the initial 10 tuples
+ */
+ SPI_cursor_fetch(portal, true, 10);
+ tuptab = SPI_tuptable;
+ n = SPI_processed;
+
+ 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 the query didn't return any rows, set the target to NULL and return
+ * with FOUND = false.
+ */
+ if (n == 0)
+ exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
+ else
+ found = true; /* processed at least one tuple */
+
+ /*
+ * Now do the loop
+ */
+ while (n > 0)
+ {
+ int i;
+
+ for (i = 0; i < n; i++)
+ {
+ int rc;
+
+ /*
+ * Assign the tuple to the target
+ */
+ exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc);
+
+ /*
+ * Execute the statements
+ */
+ rc = exec_stmts(estate, stmt->body);
+
+ if (rc != PLPGSQL_RC_OK)
+ {
+ if (rc == PLPGSQL_RC_EXIT)
+ {
+ if (estate->exitlabel == NULL)
+ /* unlabelled exit, finish the current loop */
+ rc = PLPGSQL_RC_OK;
+ else if (stmt->label != NULL &&
+ strcmp(stmt->label, estate->exitlabel) == 0)
+ {
+ /* labelled exit, matches the current stmt's label */
+ estate->exitlabel = NULL;
+ rc = PLPGSQL_RC_OK;
+ }
+
+ /*
+ * otherwise, we processed a labelled exit that does not
+ * match the current statement's label, if any: return
+ * RC_EXIT so that the EXIT continues to recurse upward.
+ */
+ }
+ else if (rc == PLPGSQL_RC_CONTINUE)
+ {
+ if (estate->exitlabel == NULL)
+ /* unlabelled continue, continue the current loop */
+ continue;
+ else if (stmt->label != NULL &&
+ strcmp(stmt->label, estate->exitlabel) == 0)
+ {
+ /* labelled continue, matches the current stmt's label */
+ estate->exitlabel = NULL;
+ continue;
+ }
+
+ /*
+ * otherwise, we process a labelled continue that does not
+ * match the current statement's label, so propagate
+ * RC_CONTINUE upward in the stack.
+ */
+ }
+
+ /*
+ * We're aborting the loop, so cleanup and set FOUND. (This
+ * code should match the code after the loop.)
+ */
+ SPI_freetuptable(tuptab);
+ exec_set_found(estate, found);
+
+ return rc;
+ }
+ }
+
+ SPI_freetuptable(tuptab);
+
+ /*
+ * Fetch the next 50 tuples
+ */
+ SPI_cursor_fetch(portal, true, 50);
+ n = SPI_processed;
+ tuptab = SPI_tuptable;
+ }
+
+ /*
+ * Release last group of tuples
+ */
+ SPI_freetuptable(tuptab);
+
+ /*
+ * Set the FOUND variable to indicate the result of executing the loop
+ * (namely, whether we looped one or more times). This must be set here so
+ * that it does not interfere with the value of the FOUND variable inside
+ * the loop processing itself.
+ */
+ exec_set_found(estate, found);
+
+ return PLPGSQL_RC_OK;
+ }
+
+ /*
+ * Build up the values and nulls arguments for SPI_execute_plan
+ */
+ static void
+ eval_expr_params(PLpgSQL_execstate *estate,
+ PLpgSQL_expr *expr, Datum **p_values, char **p_nulls)
+ {
+ Datum *values;
+ bool *nulls;
+ int i;
+
+ *p_values = values = (Datum *) palloc(expr->nparams * sizeof(Datum));
+ *p_nulls = nulls = (char *) palloc(expr->nparams * sizeof(char));
+
+ for (i = 0; i < expr->nparams; i++)
+ {
+ PLpgSQL_datum *datum = estate->datums[expr->params[i]];
+ Oid paramtypeid;
+ bool paramisnull;
+
+ exec_eval_datum(estate, datum, expr->plan_argtypes[i],
+ ¶mtypeid, &values[i], ¶misnull);
+ if (paramisnull)
+ nulls[i] = 'n';
+ else
+ nulls[i] = ' ';
+ }
+ }
+
+ /*
+ * pfree variable and assign new text constant
+ */
+ static void
+ SetTextVar(PLpgSQL_var *var, const char *str)
+ {
+ if (var->freeval)
+ pfree(DatumGetPointer(var->value));
+
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(str));
+ var->isnull = false;
+ var->freeval = true;
+ }
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2007-11-27 20:58:44.000000000 +0100
--- ./src/pl/plpgsql/src/pl_funcs.c 2007-11-28 20:23:03.000000000 +0100
***************
*** 482,487 ****
--- 482,489 ----
return _("FOR with integer loop variable");
case PLPGSQL_STMT_FORS:
return _("FOR over SELECT rows");
+ case PLPGSQL_STMT_FORC:
+ return _("FOR over cursor rows");
case PLPGSQL_STMT_EXIT:
return "EXIT";
case PLPGSQL_STMT_RETURN:
***************
*** 528,533 ****
--- 530,536 ----
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_exit(PLpgSQL_stmt_exit *stmt);
static void dump_return(PLpgSQL_stmt_return *stmt);
static void dump_return_next(PLpgSQL_stmt_return_next *stmt);
***************
*** 581,586 ****
--- 584,592 ----
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_EXIT:
dump_exit((PLpgSQL_stmt_exit *) stmt);
break;
***************
*** 776,781 ****
--- 782,810 ----
}
static void
+ dump_forc(PLpgSQL_stmt_forc *stmt)
+ {
+ dump_ind();
+ printf("FORC %s ", stmt->rec->refname);
+ printf("OPEN curvar=%d\n", stmt->curvar);
+
+ dump_indent += 2;
+ if (stmt->argquery != NULL)
+ {
+ dump_ind();
+ printf(" arguments = '");
+ dump_expr(stmt->argquery);
+ printf("'\n");
+ }
+ dump_indent -= 2;
+
+ dump_stmts(stmt->body);
+
+ dump_ind();
+ printf(" ENDFORC\n");
+ }
+
+ static void
dump_open(PLpgSQL_stmt_open *stmt)
{
dump_ind();
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2007-11-27 20:58:44.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h 2007-11-28 20:23:03.000000000 +0100
***************
*** 80,85 ****
--- 80,86 ----
PLPGSQL_STMT_WHILE,
PLPGSQL_STMT_FORI,
PLPGSQL_STMT_FORS,
+ PLPGSQL_STMT_FORC,
PLPGSQL_STMT_EXIT,
PLPGSQL_STMT_RETURN,
PLPGSQL_STMT_RETURN_NEXT,
***************
*** 409,414 ****
--- 410,430 ----
} PLpgSQL_stmt_fori;
+ /*
+ * PLpgSQL_stmt_forq is supertype for PLpgSQL_stmt_fors,
+ * PLpgSQL_stmt_forc and PLpgSQL_dynfors
+ */
+ typedef struct
+ {
+ int cmd_type;
+ int lineno;
+ char *label;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row;
+ List *body; /* List of statements */
+ } PLpgSQL_stmt_forq;
+
+
typedef struct
{ /* FOR statement running over SELECT */
int cmd_type;
***************
*** 416,435 ****
char *label;
PLpgSQL_rec *rec;
PLpgSQL_row *row;
- PLpgSQL_expr *query;
List *body; /* List of statements */
} PLpgSQL_stmt_fors;
typedef struct
{ /* FOR statement running over EXECUTE */
int cmd_type;
int lineno;
char *label;
PLpgSQL_rec *rec;
PLpgSQL_row *row;
- PLpgSQL_expr *query;
List *body; /* List of statements */
} PLpgSQL_stmt_dynfors;
--- 432,464 ----
char *label;
PLpgSQL_rec *rec;
PLpgSQL_row *row;
List *body; /* List of statements */
+ PLpgSQL_expr *query;
} PLpgSQL_stmt_fors;
typedef struct
+ {
+ int cmd_type; /* FOR statement running over cursor */
+ int lineno;
+ char *label;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row; /* not used, only for compatibility with PLpgSQL_stmt_forq */
+ List *body; /* List of statements */
+ int curvar;
+ PLpgSQL_expr *argquery;
+ } PLpgSQL_stmt_forc;
+
+
+ typedef struct
{ /* FOR statement running over EXECUTE */
int cmd_type;
int lineno;
char *label;
PLpgSQL_rec *rec;
PLpgSQL_row *row;
List *body; /* List of statements */
+ PLpgSQL_expr *query;
} PLpgSQL_stmt_dynfors;
***************
*** 736,741 ****
--- 765,771 ----
extern PLpgSQL_variable *plpgsql_build_variable(const char *refname, int lineno,
PLpgSQL_type *dtype,
bool add2namespace);
+ extern PLpgSQL_rec *plpgsql_build_record(const char *refname, int lineno);
extern PLpgSQL_condition *plpgsql_parse_err_condition(char *condname);
extern void plpgsql_adddatum(PLpgSQL_datum *new);
extern int plpgsql_add_initdatums(int **varnos);
*** ./src/test/regress/expected/plpgsql.out.orig 2007-11-30 15:04:12.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out 2007-11-30 15:02:03.000000000 +0100
***************
*** 3128,3130 ****
--- 3128,3191 ----
c9f0f895fb98ab9159f51fd0297e236d | 8 | t
(9 rows)
+ -- tests for FORC
+ create or replace function forc01()
+ returns void as
+ $$
+ declare
+ c cursor(r1 integer, r2 integer)
+ for select * from generate_series(1,20) g(i)
+ where i between r1 and r2;
+ begin
+ for r in c(5,7) loop
+ raise notice '%', r.i;
+ end loop;
+ -- second, test if cursor was closed
+ for r in c(9,10) loop
+ raise notice '%', r.i;
+ end loop;
+ return;
+ end;
+ $$ language plpgsql;
+ select forc01();
+ NOTICE: 5
+ NOTICE: 6
+ NOTICE: 7
+ NOTICE: 9
+ NOTICE: 10
+ forc01
+ --------
+
+ (1 row)
+
+ -- fail because cursor has (hasnot) params
+ create or replace function forc02()
+ returns void as
+ $$
+ declare
+ c cursor(r1 integer, r2 integer)
+ for select * from generate_series(1,20) g(i)
+ where i between r1 and r2;
+ begin
+ for r in c loop
+ raise notice '%', r.i;
+ end loop;
+ end;
+ $$ language plpgsql;
+ ERROR: cursor "c" has arguments
+ CONTEXT: compile of PL/pgSQL function "forc02" near line 6
+ create or replace function forc03()
+ returns void as
+ $$
+ declare
+ c cursor
+ for select * from generate_series(1,5) g(i);
+ begin
+ for r in c(10) loop
+ raise notice '%', r.i;
+ end loop;
+ end;
+ $$ language plpgsql;
+ ERROR: cursor "c" has no arguments
+ CONTEXT: compile of PL/pgSQL function "forc03" near line 5
+ drop function forc01();
*** ./src/test/regress/sql/plpgsql.sql.orig 2007-11-30 14:57:37.000000000 +0100
--- ./src/test/regress/sql/plpgsql.sql 2007-11-30 15:01:02.000000000 +0100
***************
*** 2582,2584 ****
--- 2582,2635 ----
select * from ret_query2(8);
+ -- tests for FORC
+ create or replace function forc01()
+ returns void as
+ $$
+ declare
+ c cursor(r1 integer, r2 integer)
+ for select * from generate_series(1,20) g(i)
+ where i between r1 and r2;
+ begin
+ for r in c(5,7) loop
+ raise notice '%', r.i;
+ end loop;
+ -- second, test if cursor was closed
+ for r in c(9,10) loop
+ raise notice '%', r.i;
+ end loop;
+ return;
+ end;
+ $$ language plpgsql;
+
+ select forc01();
+
+ -- fail because cursor has (hasnot) params
+ create or replace function forc02()
+ returns void as
+ $$
+ declare
+ c cursor(r1 integer, r2 integer)
+ for select * from generate_series(1,20) g(i)
+ where i between r1 and r2;
+ begin
+ for r in c loop
+ raise notice '%', r.i;
+ end loop;
+ end;
+ $$ language plpgsql;
+
+ create or replace function forc03()
+ returns void as
+ $$
+ declare
+ c cursor
+ for select * from generate_series(1,5) g(i);
+ begin
+ for r in c(10) loop
+ raise notice '%', r.i;
+ end loop;
+ end;
+ $$ language plpgsql;
+
+ drop function forc01();