The second type of control structure is through iteration. It controls the execution of your programs. This is done through the commands: LOOP , WHILE , FOR , and EXIT . The syntax for a simple unconditional LOOP statement is:

 LOOP 
     statements
 END LOOP;
   

An unconditional loop statement will continue to loop until it reaches an EXIT . The EXIT command explicitly terminates unconditional loops. The syntax for the EXIT command is:

 EXIT [label] [WHEN expression];
  

The label specifies the level of nesting that should be terminated. Label is the current or an upper level loop block. If a label is not specified, then the innermost loop is terminated and it jumps to the statement after the END LOOP .

For instance, the loop_addtwo() function counts by two and continues to loop until it reaches 20. It begins the count at the number supplied by the user:

Example 9-23. PL/pgSQL Loop

   CREATE FUNCTION loop_addtwo(integer) RETURNS integer AS'
   DECLARE
     -- defines a name for the input number
     num1 ALIAS FOR $1;

     -- defines a variable to hold the answer
     answer integer;
 
   BEGIN     
     -- assigns the input number into the answer
     answer = num1;

     -- adds the starting number into the table 
     INSERT INTO count_by_two VALUES (answer);

     LOOP
       -- counts by 2 each time this loop is entered
       answer = answer + 2;
       -- adds each resulting number into the count_by_two table
       INSERT INTO count_by_two VALUES(answer);
       -- exits the loop when the number is higher than 20
       EXIT WHEN answer >= 20;
     -- end of the loop body
     END LOOP;

   -- displays a 0 because this function 
   -- is defined to return an integer
   return 0;

   END;
   ' LANGUAGE 'plpgsql';
   

We used this command to count from 2 to 20:

   SELECT loop_addtwo(2);
   

The count_by_two table now contains these data values:

  number
 --------
      2
      4
      6
      8
     10
     12
     14
     16
     18
     20
(10 rows)
   

This loop is the most basic loop. That is why it needs to use the EXIT command to get out of the loop. If the EXIT command is not supplied, it will never reach the end of the loop.

Another way to control your programs is to use the WHILE loop. The syntax for a WHILE loop is:

  WHILE expression LOOP
     statements
  END LOOP;
   

This type of loop will continue to loop through the sequence of statements as long as the expression is true. It will automatically exit the loop when the expression becomes false. Therefore, the EXIT command is not needed when using the WHILE loop.

This function uses the WHILE loop to continue adding the number 2 to a user supplied number. It then inserts each number into the count_by_two table until it reaches the number 20.

Example 9-24. PL/pgSQL While Loop

 
   CREATE FUNCTION while_addtwo(integer) RETURNS integer AS'
   DECLARE
     -- defines a name for the input number
     num1 ALIAS FOR $1;

     -- defines a variable to hold the answer
     answer integer;

   BEGIN
     -- assigns the input number into the answer
     answer = num1;

     -- adds the starting count number 
     -- into the count_by_two table
     INSERT INTO count_by_two VALUES (answer);

     -- continues to count by 2 if the answer is less than 20
     WHILE answer < 20 LOOP

       -- adds 2 each time this loop is entered and 
       -- inserts the answer into the count_by_two table
       answer = answer + 2;
       INSERT INTO count_by_two VALUES(answer);

     -- end of the loop body
     END LOOP;

   -- displays a 0 because this function 
   -- is defined to return an integer
   return 0;

   END;
   ' LANGUAGE 'plpgsql';   
   

Try calling this function and count from 2. If you do a select * on the count_by_two table, you should see this displayed:

 
   number
  -------- 
      2
      4
      6
      8
     10
     12
     14
     16
     18
     20
(10 rows) 
 

The FOR command iterates over a range of integer values. It will only iterate through one step at a time. The syntax to use the FOR loop is:

  FOR name IN [REVERSE] expression .. expression LOOP
     statements
  END LOOP;
   

The name specified in the FOR loop syntax is automatically created as type integer. The name exists only inside of the FOR loop to be used for incrementing purposes.

The next function adds two to a user supplied number and inserts each answer into the count_by_two table.

Example 9-25. PL/pgSQL For Loop

  CREATE FUNCTION for_addtwo(integer) RETURNS integer AS'
  DECLARE
    -- defines an alias name for the input
    num1 ALIAS FOR $1;
   
    -- defines a variable to hold the answer
    answer integer;

  BEGIN
    -- assigns the input into the answer
    answer = num1;

    -- inserts the starting count number 
    -- into the count_by_two table
    INSERT INTO count_by_two VALUES (answer);
  
    -- loops through this loop 9 times 
    FOR i IN 1..9 LOOP
      -- adds two to the answer
      answer = answer + 2;
      -- inserts the answer into the count_by_two table
      INSERT INTO count_by_two VALUES (answer);
    -- end of the loop body
    END LOOP;
  
  -- displays 0 because this function 
  -- is defined to return an integer
  return 0;

  END;
  ' LANGUAGE 'plpgsql';
   

When supplied with the number 2, this function should have inserted the same numbers into the count_by_two table as the other two functions previously defined:

    number
   --------
      2
      4
      6
      8
     10
     12
     14
     16
     18
     20
(10 rows)
   

To see the difference between these functions, we will use the number 14 to call all three functions. The simple loop_addtwo () function returns this when supplied with a 14:

   number
  --------
     14
     16
     18
     20
(4 rows)
   

The while_addtwo () function returns a similar answer when supplied with a 14:

 
   number
  --------
     14
     16
     18
     20
(4 rows)
   

Lastly, the for_addtwo () function returns the following when a 14 is supplied:

   number
  --------
     14
     16
     18
     20
     22
     24
     26
     28
     30
     32
(10 rows)
   

The FOR loop will always return a different count when a number other than 2 is supplied. It does not stop counting at number 20, but continues to count until it has counted 10 times. On the other hand, the basic LOOP and WHILE loop will always stop counting when it reaches the number 20, no matter where it started counting from.