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 14 Aug 2002 14:53:56 -0000
***************
*** 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,896 ----
! There is a special variable named FOUND of
! type boolean. It will be set to true if:
!
!
!
! 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).
!
!
!
! You can use FOUND immediately after a SELECT
! INTO statement to check if an assignment is successful (that is,
! at least one row was returned by the SELECT statement). For example,
SELECT INTO myrec * FROM EMP WHERE empname = myname;
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 14 Aug 2002 14:53:57 -0000
***************
*** 1214,1236 ****
* Now do the loop
*/
exec_set_found(estate, false);
! for (;;)
{
! /*
! * Check bounds
! */
! if (stmt->reverse)
! {
! if ((int4) (var->value) < (int4) value)
! break;
! }
! else
! {
! if ((int4) (var->value) > (int4) value)
! break;
! }
! exec_set_found(estate, true);
/*
* Execute the statements
*/
--- 1214,1237 ----
* Now do the loop
*/
exec_set_found(estate, false);
!
! /*
! * Check bounds (this needs to be done once before entering the loop
! * at all)
! */
! if (stmt->reverse)
{
! if ((int4) (var->value) < (int4) value)
! return PLPGSQL_RC_OK;
! }
! else
! {
! if ((int4) (var->value) > (int4) value)
! return PLPGSQL_RC_OK;
! }
+ for (;;)
+ {
/*
* Execute the statements
*/
***************
*** 1245,1250 ****
--- 1246,1257 ----
break;
case PLPGSQL_RC_EXIT:
+ /*
+ * If we stopped iterating via EXIT, it is safe
+ * to set FOUND to true -- see note below.
+ */
+ exec_set_found(estate, true);
+
if (estate->exitlabel == NULL)
return PLPGSQL_RC_OK;
if (stmt->label == NULL)
***************
*** 1268,1275 ****
--- 1275,1306 ----
var->value--;
else
var->value++;
+
+ /*
+ * Check bounds
+ */
+ if (stmt->reverse)
+ {
+ if ((int4) (var->value) < (int4) value)
+ break;
+ }
+ else
+ {
+ if ((int4) (var->value) > (int4) value)
+ break;
+ }
+
}
+ /*
+ * We executed the loop at least once and did not RETURN
+ * prematurely, so set FOUND to true. This must be here (rather than
+ * before the loop processing starts) because the statements inside
+ * the loop might overwrite FOUND. See also the handling of EXIT
+ * above.
+ */
+ exec_set_found(estate, true);
+
return PLPGSQL_RC_OK;
}
***************
*** 1322,1328 ****
/*
* If the query didn't return any row, set the target to NULL and
! * return.
*/
if (n == 0)
{
--- 1353,1359 ----
/*
* If the query didn't return any row, set the target to NULL and
! * return with FOUND = false.
*/
if (n == 0)
{
***************
*** 1332,1342 ****
}
/*
- * There are tuples, so set found to true
- */
- exec_set_found(estate, true);
-
- /*
* Now do the loop
*/
for (;;)
--- 1363,1368 ----
***************
*** 1362,1367 ****
--- 1388,1399 ----
break;
case PLPGSQL_RC_EXIT:
+ /*
+ * If we stopped iterating via EXIT, it is safe to
+ * set FOUND to true -- see note below.
+ */
+ exec_set_found(estate, true);
+
SPI_freetuptable(tuptab);
SPI_cursor_close(portal);
***************
*** 1403,1408 ****
--- 1435,1449 ----
*/
SPI_cursor_close(portal);
+ /*
+ * We processed at least one tuple, and did not RETURN prematurely,
+ * so set FOUND to true. This must be here (rather than before
+ * the loop processing starts) because the statements inside the
+ * loop might overwrite FOUND. See also the handling of a EXIT
+ * above.
+ */
+ exec_set_found(estate, true);
+
return PLPGSQL_RC_OK;
}
***************
*** 1846,1851 ****
--- 1887,1897 ----
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 ****
--- 1967,1984 ----
{
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);
}
/*
--- 1986,1992 ----
"\n\tIf you want to discard the results, use PERFORM instead.");
default:
! elog(ERROR, "error executing query \"%s\"", expr->query);
}
/*
***************
*** 2088,2094 ****
Portal portal;
/*
! * Initialize the global found variable to false
*/
exec_set_found(estate, false);
--- 2142,2148 ----
Portal portal;
/*
! * Initialize the global FOUND variable to false
*/
exec_set_found(estate, false);
***************
*** 2153,2160 ****
tuptab = SPI_tuptable;
/*
! * If the query didn't return any row, set the target to NULL and
! * return.
*/
if (n == 0)
{
--- 2207,2214 ----
tuptab = SPI_tuptable;
/*
! * If the query didn't return any rows, set the target to NULL and
! * return with FOUND = false.
*/
if (n == 0)
{
***************
*** 2164,2174 ****
}
/*
- * There are tuples, so set found to true
- */
- exec_set_found(estate, true);
-
- /*
* Now do the loop
*/
for (;;)
--- 2218,2223 ----
***************
*** 2194,2199 ****
--- 2243,2254 ----
break;
case PLPGSQL_RC_EXIT:
+ /*
+ * If we stopped iterating via EXIT, it is safe to
+ * set FOUND to true -- see note below.
+ */
+ exec_set_found(estate, true);
+
SPI_freetuptable(tuptab);
SPI_cursor_close(portal);
***************
*** 2235,2240 ****
--- 2290,2303 ----
*/
SPI_cursor_close(portal);
+ /*
+ * We processed at least one tuple, and did not RETURN prematurely,
+ * so set FOUND to true. This must be here (rather than before
+ * the loop processing starts) because the statements inside the
+ * loop might overwrite FOUND. See also the handling of EXIT above.
+ */
+ exec_set_found(estate, true);
+
return PLPGSQL_RC_OK;
}
***************
*** 2615,2621 ****
/* ----------
! * exec_assign_expr Put an expressions result into
* a variable.
* ----------
*/
--- 2678,2684 ----
/* ----------
! * 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 14 Aug 2002 14:53:57 -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 14 Aug 2002 14:53:57 -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;