The FOUND Global Variable

When the top-level code block begins, PL/pgSQL declares a global variable named FOUND, which allows for a quick check of the status of the last SQL statement executed. The FOUND variable is of Boolean type and is set to true if the last statement succeeds, or, in other words, if at least one row was processed by the statement. As an example, Listing 38 shows a very simple check to see if the files table is empty or not. The table is read via PERFORM and, if at least one row is found, the FOUND Boolean variable is implicitly set to true:

testdb=> DO $code$
 BEGIN
    PERFORM pk FROM files;
    IF FOUND THEN
       RAISE DEBUG 'The files tables contain some data';

END IF;
 END $code$;

DEBUG:  The files tables contain some data

The FOUND variable is defined with a false value and is turned to true only when:

• SELECT INTO is assigned a row value
• PERFORM throws away at least one tuple
• INSERT, UPDATE, or DELETE affects at least one tuple
• FOR or FOREACH has iterated at least once
• A cursor has found a next tuple.

Please note that the FOUND variable is set after any of these statements have been executed. Listing 39 shows a possible work-flow to better explain the possible values. As you can see, FOUND begins with a false value. Entering the FOR iteration does not change its value, even if one or more tuples have been extracted from the iteration query. The FOUND value will be changed at the end of the statement, which, in this case, is the FOR loop itself. Within the FOR loop, one PERFORM is fails and one succeeds, making the values of FOUND change after each statement. The last PERFORM executed in the loop fails, giving FOUND a false value. Once the FOR loop completes, however, the value is changed to true, because FOR has iterated at least once:

testdb=> DO $code$
 DECLARE
  current_record record;
 BEGIN
  RAISE DEBUG 'In the beginning FOUND = %', FOUND;
  FOR current_record IN SELECT * FROM files LIMIT 3 LOOP
   RAISE DEBUG 'While iterating FOUND = %', FOUND;
   IF current_record.pk % 2 = 0 THEN
     -- this statement will fail
     PERFORM pk FROM files
     WHERE f_hash = 'FAIL' || current_record.f_hash;
     RAISE DEBUG 'After a failing statement FOUND = %', FOUND;
   ELSE
      -- this statement will succeed
      PERFORM pk FROM files
      WHERE f_hash = current_record.f_hash;
      RAISE DEBUG 'After a succeeding statement FOUND = %', FOUND;
   END IF;
  END LOOP;
  RAISE DEBUG 'Outside the loop FOUND = %', FOUND;
 END $code$;

DEBUG:  In the beginning FOUND = f
DEBUG:  While iterating FOUND = f
DEBUG:  After a failing statement FOUND = f
DEBUG:  While iterating FOUND = f
DEBUG:  After a succeeding statement FOUND = t
DEBUG:  While iterating FOUND = t
DEBUG:  After a failing statement FOUND = f
DEBUG:  Outside the loop FOUND = t

The FOUND variable is global across nested code blocks. This means a nested block will not redefine FOUND with a false value, but will instead keep the outer block value. As you can imagine, FOUND can be masked by an explicit declaration of a variable of the same name, but this will throw away the capability to test the result of an SQL statement that has just been executed.