*** ./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 ---- + <literal>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();