This patch improves the behavior of FOUND in PL/PgSQL. In Oracle,

FOUND is set whenever a SELECT INTO returns > 0 rows, *or* when an
INSERT, UPDATE, or DELETE affects > 0 rows. We implemented the first
part of this behavior, but not the second.

I also improved the documentation on the various situations in which
FOUND can be set (excluding inside FOR loops, which I still need to
think about), and added some regression tests for this behavior.

Neil Conway

4 files changed, 289 insertions, 130 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9a58e6b8e3..3e90f32665 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.1 2002/07/30 19:36:10 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.2 2002/08/20 05:28:23 momjian Exp $
 -->
 
 <chapter id="plpgsql"> 
@@ -126,7 +126,7 @@ END;
     them to define operators or use them in functional indexes.
    </para>
   <sect2 id="plpgsql-advantages">
-   <title>Advantages of Using PL/pgSQL</title>
+   <title>Advantages of Using <application>PL/pgSQL</application></title>
 
    <itemizedlist>
     <listitem>
@@ -852,10 +852,58 @@ SELECT INTO <replaceable>target</replaceable> <replaceable>expressions</replacea
     </para>
 
     <para>
-     There is a special variable named FOUND of type
-     <type>boolean</type> 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,
+	 There is a special variable named <literal>FOUND</literal> of
+     type <type>boolean</type>. The initial value of
+     <literal>FOUND</literal> is false; it is set to true when one of
+     the following events occurs:
+	 <itemizedlist>
+	  <listitem>
+	   <para>
+		A SELECT INTO statement is executed, and it returns one or
+		more rows.
+	   </para>
+	  </listitem>
+	  <listitem>
+	   <para>
+		A UPDATE, INSERT, or DELETE statement is executed, and it
+		affects one or more rows.
+	   </para>
+	  </listitem>
+	  <listitem>
+	   <para>
+		A PERFORM statement is executed, and it discards one or more
+		rows.
+	   </para>
+	  </listitem>
+	  <listitem>
+	   <para>
+		A FETCH statement is executed, and it returns an additional
+		row.
+	   </para>
+	  </listitem>
+	  <listitem>
+	   <para>
+		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). <literal>FOUND</literal> is only set
+		when the FOR loop exits: inside the execution of the loop,
+		<literal>FOUND</literal> is not modified, although it may be
+		set by the execution of other statements.
+	   </para>
+	  </listitem>
+	 </itemizedlist>
+	 If none of these events occur, <literal>FOUND</literal> is set to
+	 false. <literal>FOUND</literal> is a local variable; any changes
+	 to it effect only the current <application>PL/pgSQL</application>
+	 function.
+	</para>
+
+	<para>
+	 You can use <literal>FOUND</literal> 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:
   
 <programlisting>
 SELECT INTO myrec * FROM EMP WHERE empname = myname;
@@ -902,10 +950,10 @@ PERFORM <replaceable>query</replaceable>;
 
      This executes a <literal>SELECT</literal>
      <replaceable>query</replaceable> and discards the
-     result. <application>PL/pgSQL</application> 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.
+     result. <application>PL/pgSQL</application> variables are
+     substituted in the query as usual.  Also, the special variable
+     <literal>FOUND</literal> is set to true if the query produced at
+     least one row, or false if it produced no rows.
     </para>
 
     <note>
@@ -1638,8 +1686,8 @@ FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
         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.
+	SELECT INTO, the special variable <literal>FOUND</literal> may be
+	checked to see whether a row was obtained or not.
 
 <programlisting>
 FETCH curs1 INTO rowvar;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index abfce9b8bd..1ce072c578 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -3,7 +3,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.56 2002/06/24 23:12:06 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.57 2002/08/20 05:28:23 momjian Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -1180,7 +1180,8 @@ exec_stmt_fori(PLpgSQL_execstate * estate, PLpgSQL_stmt_fori * stmt)
 	Datum		value;
 	Oid			valtype;
 	bool		isnull = false;
-	int			rc;
+	bool		found = false;
+	int			rc = PLPGSQL_RC_OK;
 
 	var = (PLpgSQL_var *) (estate->datums[stmt->var->varno]);
 
@@ -1213,7 +1214,6 @@ exec_stmt_fori(PLpgSQL_execstate * estate, PLpgSQL_stmt_fori * stmt)
 	/*
 	 * Now do the loop
 	 */
-	exec_set_found(estate, false);
 	for (;;)
 	{
 		/*
@@ -1229,36 +1229,36 @@ exec_stmt_fori(PLpgSQL_execstate * estate, PLpgSQL_stmt_fori * stmt)
 			if ((int4) (var->value) > (int4) value)
 				break;
 		}
-		exec_set_found(estate, true);
+
+		found = true;	/* looped at least once */
 
 		/*
 		 * Execute the statements
 		 */
 		rc = exec_stmts(estate, stmt->body);
 
-		/*
-		 * Check returncode
-		 */
-		switch (rc)
+		if (rc == PLPGSQL_RC_RETURN)
+			break;						/* return from function */
+		else if (rc == PLPGSQL_RC_EXIT)
 		{
-			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;
+			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;
-				return PLPGSQL_RC_OK;
+				rc = PLPGSQL_RC_OK;
+			}
 
-			case PLPGSQL_RC_RETURN:
-				return PLPGSQL_RC_RETURN;
+			/*
+			 * 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.
+			 */
 
-			default:
-				elog(ERROR, "unknown rc %d from exec_stmts()", rc);
+			break;
 		}
 
 		/*
@@ -1270,7 +1270,15 @@ exec_stmt_fori(PLpgSQL_execstate * estate, PLpgSQL_stmt_fori * stmt)
 			var->value++;
 	}
 
-	return PLPGSQL_RC_OK;
+	/*
+	 * 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,16 +1296,12 @@ exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
 	PLpgSQL_row *row = NULL;
 	SPITupleTable *tuptab;
 	Portal		portal;
-	int			rc;
+	bool		found = false;
+	int			rc = PLPGSQL_RC_OK;
 	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)
@@ -1321,25 +1325,18 @@ exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
 	tuptab = SPI_tuptable;
 
 	/*
-	 * If the query didn't return any row, set the target to NULL and
-	 * return.
+	 * 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);
-		SPI_cursor_close(portal);
-		return PLPGSQL_RC_OK;
-	}
-
-	/*
-	 * There are tuples, so set found to true
-	 */
-	exec_set_found(estate, true);
+	else
+		found = true;	/* processed at least one tuple */
 
 	/*
 	 * Now do the loop
 	 */
-	for (;;)
+	while (n > 0)
 	{
 		for (i = 0; i < n; i++)
 		{
@@ -1353,35 +1350,36 @@ exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
 			 */
 			rc = exec_stmts(estate, stmt->body);
 
-			/*
-			 * Check returncode
-			 */
-			switch (rc)
+			if (rc != PLPGSQL_RC_OK)
 			{
-				case PLPGSQL_RC_OK:
-					break;
-
-				case PLPGSQL_RC_EXIT:
-					SPI_freetuptable(tuptab);
-					SPI_cursor_close(portal);
-
+				/*
+				 * 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)
-						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);
+						/* 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;
+					}
 
-					return PLPGSQL_RC_RETURN;
+					/*
+					 * 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.
+					 */
+				}
 
-				default:
-					elog(ERROR, "unknown rc %d from exec_stmts()", rc);
+				return rc;
 			}
 		}
 
@@ -1393,9 +1391,6 @@ exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
 		SPI_cursor_fetch(portal, true, 50);
 		n = SPI_processed;
 		tuptab = SPI_tuptable;
-
-		if (n == 0)
-			break;
 	}
 
 	/*
@@ -1403,14 +1398,22 @@ exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt)
 	 */
 	SPI_cursor_close(portal);
 
-	return PLPGSQL_RC_OK;
+	/*
+	 * 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,6 +1849,11 @@ exec_stmt_execsql(PLpgSQL_execstate * estate,
 	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,9 +1929,18 @@ exec_stmt_execsql(PLpgSQL_execstate * estate,
 	{
 		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,8 +1948,7 @@ exec_stmt_execsql(PLpgSQL_execstate * estate,
 				 "\n\tIf you want to discard the results, use PERFORM instead.");
 
 		default:
-			elog(ERROR, "error executing query \"%s\"",
-				 expr->query);
+			elog(ERROR, "error executing query \"%s\"", expr->query);
 	}
 
 	/*
@@ -2078,7 +2094,7 @@ exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
 	PLpgSQL_rec *rec = NULL;
 	PLpgSQL_row *row = NULL;
 	SPITupleTable *tuptab;
-	int			rc;
+	int			rc = PLPGSQL_RC_OK;
 	int			i;
 	int			n;
 	HeapTuple	typetup;
@@ -2086,11 +2102,7 @@ exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
 	FmgrInfo	finfo_output;
 	void	   *plan;
 	Portal		portal;
-
-	/*
-	 * Initialize the global found variable to false
-	 */
-	exec_set_found(estate, false);
+	bool		found = false;
 
 	/*
 	 * Determine if we assign to a record or a row
@@ -2153,25 +2165,18 @@ exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
 	tuptab = SPI_tuptable;
 
 	/*
-	 * If the query didn't return any row, set the target to NULL and
-	 * return.
+	 * 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);
-		SPI_cursor_close(portal);
-		return PLPGSQL_RC_OK;
-	}
-
-	/*
-	 * There are tuples, so set found to true
-	 */
-	exec_set_found(estate, true);
+	else
+		found = true;
 
 	/*
 	 * Now do the loop
 	 */
-	for (;;)
+	while (n > 0)
 	{
 		for (i = 0; i < n; i++)
 		{
@@ -2186,34 +2191,35 @@ exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
 			rc = exec_stmts(estate, stmt->body);
 
 			/*
-			 * Check returncode
+			 * We're aborting the loop, so cleanup and set FOUND
 			 */
-			switch (rc)
+			if (rc != PLPGSQL_RC_OK)
 			{
-				case PLPGSQL_RC_OK:
-					break;
-
-				case PLPGSQL_RC_EXIT:
-					SPI_freetuptable(tuptab);
-					SPI_cursor_close(portal);
+				exec_set_found(estate, found);
+				SPI_freetuptable(tuptab);
+				SPI_cursor_close(portal);
 
+				if (rc == 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:
-					SPI_freetuptable(tuptab);
-					SPI_cursor_close(portal);
+						/* 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;
+					}
 
-					return PLPGSQL_RC_RETURN;
+					/*
+					 * 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.
+					 */
+				}
 
-				default:
-					elog(ERROR, "unknown rc %d from exec_stmts()", rc);
+				return rc;
 			}
 		}
 
@@ -2225,9 +2231,6 @@ exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
 		SPI_cursor_fetch(portal, true, 50);
 		n = SPI_processed;
 		tuptab = SPI_tuptable;
-
-		if (n == 0)
-			break;
 	}
 
 	/*
@@ -2235,6 +2238,14 @@ exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
 	 */
 	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,7 +2626,7 @@ exec_stmt_close(PLpgSQL_execstate * estate, PLpgSQL_stmt_close * stmt)
 
 
 /* ----------
- * exec_assign_expr			Put an expressions result into
+ * exec_assign_expr			Put an expression's result into
  *					a variable.
  * ----------
  */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index cb40912a42..91d7c34b66 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -1534,3 +1534,59 @@ SELECT recursion_test(4,3);
  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)
+
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 6ce6e364e6..618273823b 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1414,3 +1414,47 @@ BEGIN
 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;