The basic loop

Use the LOOP keyword to begin a basic, unconditional loop within a function. An unconditional loop will execute the statements within its body until an EXIT statement is reached. To form an EXIT statement, the EXIT keyword can be accompanied by WHEN, followed by and an expression to specify when the loop should exit. Th expression should be a Boolean expression, such as one that checks to see whether a variable has reached a specified value. Following is the syntax (without the ELSE keyword) for an unconditional loop:

    LOOP
      
statement;

      [...]
    END LOOP;

An unconditional loop statement will continue to loop until it reaches an EXIT statement. EXIT statements explicitly terminate unconditional loops. When terminating a loop with EXIT, you may optionally specify a label and/or a condition on which the loop should exit from.

A label is an arbitrary identifier, prefixed with a pair of less-than symbols (<<) and suffixed with a pair of greater-than symbols (>>). In the case of a loop, it may be placed directly before the loop block begins to identify that loop block with a chosen label. Here is an example of a defined loop with label syntax:

  <<
label_name
>>
  LOOP
    [ ... ]
  END LOOP;

By providing a label, you can specify which loop to exit when you have several loops nested inside each other (the use of labels in EXIT will only work if you have specified a label for the loop you are attempting to terminate).

By providing a condition in an EXIT statement specifies that the loop should be terminated when the condition is true.

Here is the syntax for an EXIT statement, within a LOOP:

  [ <<
label
>> ]
  LOOP
    
statement;

    [...]
    EXIT [ 
label
 ] [ WHEN 
condition
 ];
  END LOOP;

Example 11-42 shows a demonstration of an unconditional loop and an EXIT statement that ends it based on a condition. The square_integer_loop() function squares an integer (multiplies the number by itself) until it reaches a value higher than ten thousand. The function then returns the resulting value.

CREATE FUNCTION square_integer_loop (integer) RETURNS integer AS '
  DECLARE
     
     -- Declare aliases for function argument.
    num1 ALIAS FOR $1;
     
     -- Declare an integer to hold the result.
    result integer;
  
  BEGIN
     
     -- Assign the user input number to the result variable.
    result := num1;
    
    LOOP
      result := result * result;
      EXIT WHEN result >= 10000;
    END LOOP;
    
    RETURN result;
  END;
' LANGUAGE 'plpgsql';

Example 11-43 shows the result of invoking square_integer_loop() and passing the value 3 as an argument.

booktown=# 
SELECT square_integer_loop(3);

 square_integer_loop
---------------------
                6561
(1 row)

The WHILE loop

The WHILE loop is used to loop through a block of statements until a specified condition becomes false. Each time a WHILE loop is entered, its condition will be evaluated before the statement block is executed.

If the condition is evaluated as TRUE, the statements will then be executed. If the condition is never evaluated as false, the statement block will repeatedly executed until the client process that it originated from is terminated. The syntax of the WHILE loop is shown here:

  [ <<
label
>> ]
  WHILE 
condition
 LOOP
    
statement;

    [...]
  END LOOP;

In Example 11-44, the add_two_loop( ) function demonstrates the use of a WHILE loop designed to add one to a number until the number reaches a specified value. The starting number and ending number are both supplied by the user as function arguments. The != symbol in Example 11-44 is the inequality operator. That inequality operator indicates that the WHILE loop will run while the result variable is not equal to the high_number variable. In other words, the WHILE loop in Example 11-44 will run until result is equal to high_number.

CREATE FUNCTION add_two_loop (integer, integer) RETURNS integer AS '
  DECLARE
     
     -- Declare aliases for function arguments.
    low_number ALIAS FOR $1;
    high_number ALIAS FOR $2;
     
     -- Declare a variable to hold the result.
    result INTEGER = 0;
  
  BEGIN
     
     -- Add one to the variable result until the value of result is
     -- equal to high_number.
    WHILE result != high_number LOOP
      result := result + 1;
    END LOOP;
    
    RETURN result;
  END;
' LANGUAGE 'plpgsql';

The FOR loop

The FOR loop is arguably the most important loop implemented in PL/pgSQL. Use the FOR loop to iterate a statement block over a range of integers that you specify. The structure of a FOR loop in PL/pgSQL is similar to FOR loops in other procedural languages, such as C.

In a PL/pgSQL FOR loop an integer variable is stated first, to track the iteration of the loop, then the integer range is given, and finally a statement block is provided. The integer variable created to track the loop's iteration is destroyed once the loop exits; it does not have to be declared in the declaration section of the block. The following shows the syntax of the FOR loop:

  [ <<
label
>> ]
  FOR 
identifier
 IN [ REVERSE ] 
expression1
 .. 
expression2
  LOOP
      
statement;

      [...]
  END LOOP;

The FOR loop will perform a single iteration for each incremented value of identifier which is in the range of values between, and including, expression1 and expression2 . The identifier value will be initialized to the value of expression1 , regardless of any prior settings, and incremented by one each iteration. If REVERSE is specified, identifier will be decremented rather than incremented.

The FOR loop can also be used to cycle through the the results of a query. The second FOR loop in Example 11-45 demonstrates using a FOR loop to work with RECORD and %ROWTYPE variables. The syntax of a FOR loop that iterates through RECORD and %ROWTYPE variables is shown in the following syntax:

  [ <<
label
>> ]
  FOR { 
record_variable
 | 
%rowtype_variable
 } IN 
select_statement
 LOOP
    
statement;

    [...]
  END LOOP;

In Example 11-45, the extract_all_titles() function is used to extract a list of all book titles that exist on the database, organized by subject. When a subject has no book titles, a blank line is displayed. The list is returned as a text variable. A FOR loop is utilized within the extract_all_titles() function to cycle through the available subjects by number.

Another FOR loop is nested within the original loop to cycle through the available books and retrieve all books with subject_id values that match the original loop's iteration variable, which represents the current subject ID number the function is scanning for. In Example 11-45, the iteration variable i is initialized to zero because the first subject ID number in our subjects table is 0.

CREATE FUNCTION extract_all_titles2 () RETURNS text AS '
  DECLARE
     
     -- Declare a variable for the subject ID number.
    sub_id INTEGER;
     
     -- Declare a variable to hold the list of titles.
    text_output TEXT = '' '';
     
     -- Declare a variable to hold the subject title.
    sub_title TEXT;
     
     -- Declare a variable to hold records from the  books table.
    row_data books%ROWTYPE;
  
  BEGIN
     
     -- Outer FOR loop: loop through the body of this loop until the
     -- variable i equals 15.  Start the looping at 0.  Essentially,
     --loop the following statements 16 times (once for each subject).
    FOR i IN 0..15 LOOP
       
       -- Retrieve the subject name of the subject with an ID number
       -- that matches the variable i.
      SELECT INTO sub_title subject FROM subjects WHERE id = i;
       
       -- Insert the subject name, a colon, and a new line into the
       -- text_output variable.
      text_output = text_output || ''\n'' || sub_title || '':\n'';
       
       -- Loop through all records in the books table with a subject ID
       -- that matches the variable i.
      FOR row_data IN SELECT * FROM books
        WHERE subject_id = i  LOOP
         
         -- Insert the title of a matching book into the text_output
         -- variable, followed by a newline.
        text_output := text_output || row_data.title || ''\n'';
      
      END LOOP;
    END LOOP;
     
     -- Return the list.
    RETURN text_output;
  END;
' LANGUAGE 'plpgsql';

Example 11-46 shows the code of another function that uses a FOR loop to iterate through the results of a SQL query. With each iteration of the loop the FOR loop in Example 11-46 places the contents of a result row from a query against the books table into the row_data variable, and then inserts the value of the row's title field into the text_output variable.

The loop ends when the last record in books is reached. By the end of the loop, text_output will contain a list of all book titles that match the subject ID number passed to the function. The text_output variable is returned at the end of the function.

CREATE FUNCTION extract_title (integer) RETURNS text AS '
  DECLARE
     
     -- Declare an alias for function argument.
    sub_id ALIAS FOR $1;
     
     -- Declare a variable to hold book titles and set its default
     --  value to a new line.
    text_output TEXT :=''\n'';
     
     -- Declare a variable to hold rows from the
     -- books table.
    row_data books%ROWTYPE;
  
  BEGIN
     
     -- Iterate through the results of a query.
    FOR row_data IN SELECT * FROM books
    WHERE subject_id = sub_id ORDER BY title  LOOP
       
       -- Insert the title of a matching book into the text_output variable.
      text_output := text_output || row_data.title || ''\n'';
    END LOOP;
      
      -- Return the list of books.
    RETURN text_output;
  END;
' LANGUAGE 'plpgsql';

Example 11-47 shows the results of the extract_title() function when 2, which represents "Children's Books" in the subject table, is passed as an argument.

booktown=# 
SELECT extract_title(2);

                         extract_title
-------------------------------------------------------------------

Bartholomew and the Oobleck
Franklin in the Dark
Goodnight Moon
The Cat in the Hat

(1 row)

The row_data variable is declared as a %ROWTYPE of the books table because it will only be used to hold records from the books table. We could have declared row_data as a RECORD to accomplish the same result, but the RECORD type should be used when you are going to be using the variable for more than just the rows of one specific table:

row_data RECORD;

The extract_title() function will return the same results whether row data is declared as RECORD, or is declared using %ROWTYPE.