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

2 files changed, 100 insertions, 0 deletions

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;