Index: doc/src/sgml/plpgsql.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.1 diff -c -r1.1 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 30 Jul 2002 19:36:10 -0000 1.1 --- doc/src/sgml/plpgsql.sgml 15 Aug 2002 02:16:28 -0000 *************** *** 126,132 **** them to define operators or use them in functional indexes. ! Advantages of Using PL/pgSQL --- 126,132 ---- them to define operators or use them in functional indexes. ! Advantages of Using <application>PL/pgSQL</application> *************** *** 852,861 **** ! There is a special variable named FOUND of type ! boolean that can be used immediately after a SELECT ! INTO to check if an assignment had success (that is, at least one ! row was returned by the SELECT). For example, SELECT INTO myrec * FROM EMP WHERE empname = myname; --- 852,909 ---- ! There is a special variable named FOUND of ! type boolean. The initial value of ! FOUND is false; it is set to true when one of ! the following events occurs: ! ! ! ! A SELECT INTO statement is executed, and it returns one or ! more rows. ! ! ! ! ! A UPDATE, INSERT, or DELETE statement is executed, and it ! affects one or more rows. ! ! ! ! ! A PERFORM statement is executed, and it discards one or more ! rows. ! ! ! ! ! A FETCH statement is executed, and it returns an additional ! row. ! ! ! ! ! A FOR statement is executed, and it iterates one or more ! times. This applies to all three variants of the FOR statement ! (integer FOR loops, record-set FOR loops, and dynamic ! record-set FOR loops). FOUND is only set ! when the FOR loop exits: inside the execution of the loop, ! FOUND is not modified, although it may be ! set by the execution of other statements. ! ! ! ! If none of these events occur, FOUND is set to ! false. FOUND is a local variable; any changes ! to it effect only the current PL/pgSQL ! function. ! ! ! ! You can use FOUND immediately after a SELECT ! INTO statement to determine whether the assignment was successful ! (that is, at least one row was was returned by the SELECT ! statement). For example: SELECT INTO myrec * FROM EMP WHERE empname = myname; *************** *** 902,911 **** This executes a SELECT query and discards the ! result. PL/pgSQL variables are substituted ! in the query as usual. Also, the special variable FOUND is set to ! true if the query produced at least one row, or false if it produced ! no rows. --- 950,959 ---- This executes a SELECT query and discards the ! result. PL/pgSQL variables are ! substituted in the query as usual. Also, the special variable ! FOUND is set to true if the query produced at ! least one row, or false if it produced no rows. *************** *** 1638,1645 **** FETCH retrieves the next row from the cursor into a target, which may be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. As with ! SELECT INTO, the special variable FOUND may be checked to see ! whether a row was obtained or not. FETCH curs1 INTO rowvar; --- 1686,1693 ---- FETCH retrieves the next row from the cursor into a target, which may be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. As with ! SELECT INTO, the special variable FOUND may be ! checked to see whether a row was obtained or not. FETCH curs1 INTO rowvar; Index: src/pl/plpgsql/src/pl_exec.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.56 diff -c -r1.56 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c 24 Jun 2002 23:12:06 -0000 1.56 --- src/pl/plpgsql/src/pl_exec.c 15 Aug 2002 02:16:28 -0000 *************** *** 1180,1186 **** Datum value; Oid valtype; bool isnull = false; ! int rc; var = (PLpgSQL_var *) (estate->datums[stmt->var->varno]); --- 1180,1187 ---- Datum value; Oid valtype; bool isnull = false; ! bool found = false; ! int rc = PLPGSQL_RC_OK; var = (PLpgSQL_var *) (estate->datums[stmt->var->varno]); *************** *** 1213,1219 **** /* * Now do the loop */ - exec_set_found(estate, false); for (;;) { /* --- 1214,1219 ---- *************** *** 1229,1264 **** if ((int4) (var->value) > (int4) value) break; } ! exec_set_found(estate, true); /* * Execute the statements */ rc = exec_stmts(estate, stmt->body); ! /* ! * Check returncode ! */ ! switch (rc) { ! case PLPGSQL_RC_OK: ! break; ! ! case PLPGSQL_RC_EXIT: ! if (estate->exitlabel == NULL) ! return PLPGSQL_RC_OK; ! if (stmt->label == NULL) ! return PLPGSQL_RC_EXIT; ! if (strcmp(stmt->label, estate->exitlabel)) ! return PLPGSQL_RC_EXIT; estate->exitlabel = NULL; ! return PLPGSQL_RC_OK; ! case PLPGSQL_RC_RETURN: ! return PLPGSQL_RC_RETURN; ! default: ! elog(ERROR, "unknown rc %d from exec_stmts()", rc); } /* --- 1229,1264 ---- if ((int4) (var->value) > (int4) value) break; } ! ! found = true; /* looped at least once */ /* * Execute the statements */ rc = exec_stmts(estate, stmt->body); ! if (rc == PLPGSQL_RC_RETURN) ! break; /* return from function */ ! else 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. ! */ ! break; } /* *************** *** 1270,1276 **** var->value++; } ! return PLPGSQL_RC_OK; } --- 1270,1284 ---- var->value++; } ! /* ! * 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; } *************** *** 1288,1303 **** PLpgSQL_row *row = NULL; SPITupleTable *tuptab; Portal portal; ! int rc; int i; int n; /* - * Initialize the global found variable to false - */ - exec_set_found(estate, false); - - /* * Determine if we assign to a record or a row */ if (stmt->rec != NULL) --- 1296,1307 ---- 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) *************** *** 1321,1345 **** tuptab = SPI_tuptable; /* ! * If the query didn't return any row, set the target to NULL and ! * return. */ if (n == 0) - { exec_move_row(estate, rec, row, NULL, NULL); ! SPI_cursor_close(portal); ! return PLPGSQL_RC_OK; ! } ! ! /* ! * There are tuples, so set found to true ! */ ! exec_set_found(estate, true); /* * Now do the loop */ ! for (;;) { for (i = 0; i < n; i++) { --- 1325,1342 ---- tuptab = SPI_tuptable; /* ! * 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, NULL); ! else ! found = true; /* processed at least one tuple */ /* * Now do the loop */ ! while (n > 0) { for (i = 0; i < n; i++) { *************** *** 1353,1387 **** */ rc = exec_stmts(estate, stmt->body); ! /* ! * Check returncode ! */ ! switch (rc) { ! case PLPGSQL_RC_OK: ! break; ! ! case PLPGSQL_RC_EXIT: ! SPI_freetuptable(tuptab); ! SPI_cursor_close(portal); if (estate->exitlabel == NULL) ! return PLPGSQL_RC_OK; ! if (stmt->label == NULL) ! return PLPGSQL_RC_EXIT; ! if (strcmp(stmt->label, estate->exitlabel)) ! return PLPGSQL_RC_EXIT; ! estate->exitlabel = NULL; ! return PLPGSQL_RC_OK; ! ! case PLPGSQL_RC_RETURN: ! SPI_freetuptable(tuptab); ! SPI_cursor_close(portal); ! return PLPGSQL_RC_RETURN; ! default: ! elog(ERROR, "unknown rc %d from exec_stmts()", rc); } } --- 1350,1385 ---- */ rc = exec_stmts(estate, stmt->body); ! if (rc != PLPGSQL_RC_OK) { ! /* ! * We're aborting the loop, so cleanup and set FOUND ! */ ! exec_set_found(estate, found); ! SPI_freetuptable(tuptab); ! SPI_cursor_close(portal); + 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. ! */ ! } ! return rc; } } *************** *** 1393,1401 **** SPI_cursor_fetch(portal, true, 50); n = SPI_processed; tuptab = SPI_tuptable; - - if (n == 0) - break; } /* --- 1391,1396 ---- *************** *** 1403,1416 **** */ SPI_cursor_close(portal); ! return PLPGSQL_RC_OK; } /* ---------- * exec_stmt_select Run a query and assign the first * row to a record or rowtype. ! * ---------- */ static int exec_stmt_select(PLpgSQL_execstate * estate, PLpgSQL_stmt_select * stmt) --- 1398,1419 ---- */ 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_select Run a query and assign the first * row to a record or rowtype. ! * ---------- */ static int exec_stmt_select(PLpgSQL_execstate * estate, PLpgSQL_stmt_select * stmt) *************** *** 1846,1851 **** --- 1849,1859 ---- bool isnull; /* + * Set magic FOUND variable to false + */ + exec_set_found(estate, false); + + /* * On the first call for this expression generate the plan */ if (expr->plan == NULL) *************** *** 1921,1929 **** --- 1929,1946 ---- { case SPI_OK_UTILITY: case SPI_OK_SELINTO: + break; + + /* + * If the INSERT, DELETE, or UPDATE query affected at least + * one tuple, set the magic 'FOUND' variable to true. This + * conforms with the behavior of PL/SQL. + */ case SPI_OK_INSERT: case SPI_OK_DELETE: case SPI_OK_UPDATE: + if (SPI_processed > 0) + exec_set_found(estate, true); break; case SPI_OK_SELECT: *************** *** 1931,1938 **** "\n\tIf you want to discard the results, use PERFORM instead."); default: ! elog(ERROR, "error executing query \"%s\"", ! expr->query); } /* --- 1948,1954 ---- "\n\tIf you want to discard the results, use PERFORM instead."); default: ! elog(ERROR, "error executing query \"%s\"", expr->query); } /* *************** *** 2078,2084 **** PLpgSQL_rec *rec = NULL; PLpgSQL_row *row = NULL; SPITupleTable *tuptab; ! int rc; int i; int n; HeapTuple typetup; --- 2094,2100 ---- PLpgSQL_rec *rec = NULL; PLpgSQL_row *row = NULL; SPITupleTable *tuptab; ! int rc = PLPGSQL_RC_OK; int i; int n; HeapTuple typetup; *************** *** 2086,2096 **** FmgrInfo finfo_output; void *plan; Portal portal; ! ! /* ! * Initialize the global found variable to false ! */ ! exec_set_found(estate, false); /* * Determine if we assign to a record or a row --- 2102,2108 ---- FmgrInfo finfo_output; void *plan; Portal portal; ! bool found = false; /* * Determine if we assign to a record or a row *************** *** 2153,2177 **** tuptab = SPI_tuptable; /* ! * If the query didn't return any row, set the target to NULL and ! * return. */ if (n == 0) - { exec_move_row(estate, rec, row, NULL, NULL); ! SPI_cursor_close(portal); ! return PLPGSQL_RC_OK; ! } ! ! /* ! * There are tuples, so set found to true ! */ ! exec_set_found(estate, true); /* * Now do the loop */ ! for (;;) { for (i = 0; i < n; i++) { --- 2165,2182 ---- tuptab = SPI_tuptable; /* ! * 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, NULL); ! else ! found = true; /* * Now do the loop */ ! while (n > 0) { for (i = 0; i < n; i++) { *************** *** 2186,2219 **** rc = exec_stmts(estate, stmt->body); /* ! * Check returncode */ ! switch (rc) { ! case PLPGSQL_RC_OK: ! break; ! ! case PLPGSQL_RC_EXIT: ! SPI_freetuptable(tuptab); ! SPI_cursor_close(portal); if (estate->exitlabel == NULL) ! return PLPGSQL_RC_OK; ! if (stmt->label == NULL) ! return PLPGSQL_RC_EXIT; ! if (strcmp(stmt->label, estate->exitlabel)) ! return PLPGSQL_RC_EXIT; ! estate->exitlabel = NULL; ! return PLPGSQL_RC_OK; ! ! case PLPGSQL_RC_RETURN: ! SPI_freetuptable(tuptab); ! SPI_cursor_close(portal); ! return PLPGSQL_RC_RETURN; ! default: ! elog(ERROR, "unknown rc %d from exec_stmts()", rc); } } --- 2191,2225 ---- rc = exec_stmts(estate, stmt->body); /* ! * We're aborting the loop, so cleanup and set FOUND */ ! if (rc != PLPGSQL_RC_OK) { ! exec_set_found(estate, found); ! SPI_freetuptable(tuptab); ! SPI_cursor_close(portal); + 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. ! */ ! } ! return rc; } } *************** *** 2225,2233 **** SPI_cursor_fetch(portal, true, 50); n = SPI_processed; tuptab = SPI_tuptable; - - if (n == 0) - break; } /* --- 2231,2236 ---- *************** *** 2235,2240 **** --- 2238,2251 ---- */ 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; } *************** *** 2615,2621 **** /* ---------- ! * exec_assign_expr Put an expressions result into * a variable. * ---------- */ --- 2626,2632 ---- /* ---------- ! * exec_assign_expr Put an expression's result into * a variable. * ---------- */ Index: src/test/regress/expected/plpgsql.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/plpgsql.out,v retrieving revision 1.6 diff -c -r1.6 plpgsql.out *** src/test/regress/expected/plpgsql.out 21 Sep 2001 00:11:31 -0000 1.6 --- src/test/regress/expected/plpgsql.out 15 Aug 2002 02:16:28 -0000 *************** *** 1534,1536 **** --- 1534,1592 ---- 4,3,2,1,3 (1 row) + -- + -- Test the FOUND magic variable + -- + CREATE TABLE found_test_tbl (a int); + create function test_found () + returns boolean as ' + declare + begin + insert into found_test_tbl values (1); + if FOUND then + insert into found_test_tbl values (2); + end if; + + update found_test_tbl set a = 100 where a = 1; + if FOUND then + insert into found_test_tbl values (3); + end if; + + delete from found_test_tbl where a = 9999; -- matches no rows + if not FOUND then + insert into found_test_tbl values (4); + end if; + + for i in 1 .. 10 loop + -- no need to do anything + end loop; + if FOUND then + insert into found_test_tbl values (5); + end if; + + -- never executes the loop + for i in 2 .. 1 loop + -- no need to do anything + end loop; + if not FOUND then + insert into found_test_tbl values (6); + end if; + return true; + end;' language 'plpgsql'; + select test_found(); + test_found + ------------ + t + (1 row) + + select * from found_test_tbl; + a + ----- + 2 + 100 + 3 + 4 + 5 + 6 + (6 rows) + Index: src/test/regress/sql/plpgsql.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/plpgsql.sql,v retrieving revision 1.5 diff -c -r1.5 plpgsql.sql *** src/test/regress/sql/plpgsql.sql 21 Sep 2001 00:11:31 -0000 1.5 --- src/test/regress/sql/plpgsql.sql 15 Aug 2002 02:16:28 -0000 *************** *** 1414,1416 **** --- 1414,1460 ---- END;' LANGUAGE 'plpgsql'; SELECT recursion_test(4,3); + + -- + -- Test the FOUND magic variable + -- + CREATE TABLE found_test_tbl (a int); + + create function test_found () + returns boolean as ' + declare + begin + insert into found_test_tbl values (1); + if FOUND then + insert into found_test_tbl values (2); + end if; + + update found_test_tbl set a = 100 where a = 1; + if FOUND then + insert into found_test_tbl values (3); + end if; + + delete from found_test_tbl where a = 9999; -- matches no rows + if not FOUND then + insert into found_test_tbl values (4); + end if; + + for i in 1 .. 10 loop + -- no need to do anything + end loop; + if FOUND then + insert into found_test_tbl values (5); + end if; + + -- never executes the loop + for i in 2 .. 1 loop + -- no need to do anything + end loop; + if not FOUND then + insert into found_test_tbl values (6); + end if; + return true; + end;' language 'plpgsql'; + + select test_found(); + select * from found_test_tbl;