Iterations

Iterations are implemented by means of loops. Each loop body is always wrapped between the LOOP and END LOOP keywords and can be assigned a label. This can be useful for jumping out of an inner loop to an external loop.

The simplest form of loop is the unconditional loop, which is repeated infinitely until an explicit EXIT is invoked. EXIT forces the current loop to stop immediately and can be subject to a Boolean condition specified by a WHEN predicate. As an example, Listing 16 and Listing 17 show the same loop that prints out a message four times:

testdb=> DO $code$
 DECLARE
   counter int := 0;
 BEGIN
   LOOP
      counter := counter + 1;
      RAISE INFO 'This is the % time I say HELLO!', counter;
      EXIT WHEN counter > 3;
   END LOOP;

   RAISE INFO 'Good bye';
 END $code$;

INFO:  This is the 1 time I say HELLO!
INFO:  This is the 2 time I say HELLO!
INFO:  This is the 3 time I say HELLO!
INFO:  This is the 4 time I say HELLO!
INFO:  Good bye

testdb=> DO $code$
 DECLARE
   counter int := 0;
 BEGIN
   LOOP
      counter := counter + 1;
      RAISE INFO 'This is the % time I say HELLO!', counter;
      IF counter > 3 THEN
         EXIT;
      END IF;
   END LOOP;
   RAISE INFO 'Good bye';
 END $code$;

Output:
This is the 1 time I say HELLO!
This is the 2 time I say HELLO!
This is the 3 time I say HELLO!
This is the 4 time I say HELLO!
Good bye

It is worth noting that the unconditional LOOP combined with an appropriate EXIT predicate can implement the do...while loop of other programming languages, such as Java:

LOOP  -- do {
  counter := counter + 1;
  ...
  EXIT WHEN counter > 10; -- } while ( counter <= 10 );
END LOOP;

As already stated, loops can be labeled, which allows EXIT to specify which loop to terminate in a nested loop. Each label is wrapped into two angled brackets and is placed immediately before the LOOP keyword. As an example, Listing 18 shows two nested loops, one labeled MAIN_LOOP and the other labeled INNER_LOOP. The inner loop can also terminate the outer loop (the main loop) with an EXIT that explicitly specifies the MAIN_LOOP label:

testdb=> DO $code$
 DECLARE
   counter       int := 0;
   inner_counter int := 0;
 BEGIN
   <<MAIN_LOOP>>
   LOOP
      counter := counter + 1;
      RAISE INFO 'This is the % time I say HELLO!', counter;
      inner_counter := 0;

      <<INNER_LOOP>>
      LOOP
        inner_counter := inner_counter + 1;
        RAISE INFO 'I do repeat: HELLO!';
        -- exit from this loop
        EXIT WHEN inner_counter >= 2;
        -- terminate also the main loop
        EXIT MAIN_LOOP WHEN inner_counter >= 4;
      END LOOP;
      EXIT WHEN counter >= 10;
   END LOOP;
   RAISE INFO 'Good bye';
 END $code$;

INFO:  This is the 1 time I say HELLO!
INFO:  I do repeat: HELLO!
INFO:  I do repeat: HELLO!
...
INFO:  This is the 10 time I say HELLO!
INFO:  I do repeat: HELLO!
INFO:  I do repeat: HELLO!
INFO:  Good bye

The counterpart of EXIT is CONTINUE. This allows the loop to restart from the very next iteration and is therefore a handy way to skip a part of code and continue the iteration. The syntax for CONTINUE is the same as EXIT and therefore it accepts an optional WHEN Boolean condition as well as an optional loop label. If no label is specified, the innermost loop is assumed to be the one from which the iteration will restart. Listing 19 shows an example of mixing EXIT and CONTINUES with nested loops:

testdb=> DO $code$
 DECLARE
   counter       int := 0;
   inner_counter int := 0;
 BEGIN
   <<MAIN_LOOP>>
   LOOP
      counter := counter + 1;
      -- skip to the next iteration if counter is a multiple of 3
      CONTINUE WHEN counter % 3 = 0;
      RAISE INFO 'This is the % time I say HELLO!', counter;
      inner_counter := 0;

      <<INNER_LOOP>>
      LOOP
        inner_counter := inner_counter + 1;
        -- restart from the outer loop when inner_counter is a multiple of 2
        CONTINUE MAIN_LOOP WHEN inner_counter % 2 = 0;
        RAISE INFO 'I do repeat: HELLO!';
        -- exit from this loop
        EXIT WHEN inner_counter > 0;
      END LOOP;
      EXIT WHEN counter >= 10;

END LOOP;
   RAISE INFO 'Good bye';
 END $code$;

The simplest form of conditional loop is WHILE. This accepts a Boolean condition to test on each iteration and can be labeled to allow CONTINUE and EXIT to identify exactly which loop to operate on. Listing 20 shows two nested WHILE loops in action:

testdb=>  DO $code$
 DECLARE
   counter       int := 0;
   inner_counter int := 0;
 BEGIN
   <<MAIN_WHILE>>
   WHILE counter < 5 LOOP
         counter := counter + 1;

         RAISE INFO 'This is the % time I say HELLO!', counter;
         inner_counter := counter;

         <<INNER_WHILE>>
         WHILE inner_counter > 0 LOOP
               RAISE INFO 'I do repeat: HELLO!';
               inner_counter := inner_counter - 1;
         END LOOP;
   END LOOP;
   RAISE INFO 'Good bye';
 END $code$;

The FOR loop evaluates expressions to determine the beginning and end of the iteration. The idea is to limit the iteration between a lower and upper integer bound with an iteration variable that gets assigned to every value between the loop boundaries, depending on an increment. The iteration variable declaration can be omitted; in this case, the variable is automatically declared to be of integer type. The lower and upper bounds can either be literal integer values or expressions that are dynamically evaluated and they appear separated by two dots: ...

The integer increment of the iteration variable is specified with the BY keyword followed by the literal value or expression. If the BY keyword is omitted, a literal increment of one unit is assumed. To summarize, the PL/pgSQL FOR loop appears as follows, compared to a Java for cycle:

FOR i IN 1 .. 10 BY 1 LOOP
  -- computation here
END LOOP;

// Java style
for ( int i = 1; i < 10; i = i + 1 ){
 // computation here
}

Listings 21 shows two equivalent simple FOR loops, where the variable counter starts from the BEGIN expression with a value of 1 and is incremented one unit at time until the end expression is reached, which is a value of 5. Both the begin and end expressions can be computed dynamically:

testdb=>  DO $code$
  BEGIN
   FOR counter IN 1 .. 5 LOOP
       RAISE INFO 'This is the % time I say HELLO', counter;
   END LOOP;
  END $code$;

The FOR loop can loop backwards when used with the REVERSE keyword, as shown in Listing 22:

testdb=> DO $code$
 BEGIN
  FOR counter IN EXTRACT( month FROM CURRENT_DATE ) .. 12 LOOP
      RAISE INFO 'The month % I say HELLO', counter;
  END LOOP;

  FOR counter IN REVERSE 5 .. 1 BY 1 LOOP
      RAISE INFO 'This is the % time I say HELLO', counter;
  END LOOP;
 END $code$;

As with other loops, FOR loops can be labeled and nested and can exploit both EXIT and CONTINUE predicates.

The FOR loop is definitely the most powerful iteration construct. It not only allows for iterations over values, but also over results sets. When iterating on query results, the cycle variable must be a record, a specific row type variable, or, in the case that single variables are extracted out of each tuple, a list of appropriate scalar variables. Listing 23 and Listing 24 do the same thing, but Listing 23 uses a record variable to iterate through results, while in Listing 24, single column variables are assigned to each iteration. As you can see, this form of FOR does not allow a BY expression, since it is the query specified after the IN clause that defines which values to use for the next iteration:

testdb=> DO $code$
 DECLARE
  current_record record;
 BEGIN
  FOR current_record IN SELECT f_name, f_size
                        FROM files
                        ORDER BY f_name
                        LOOP
      RAISE INFO 'The file % has a size of % bytes', current_record.f_name, current_record.f_size;
  END LOOP;
 END $code$;

testdb=> DO $code$
 DECLARE
  file_name text;
  file_size numeric;
 BEGIN
  FOR file_name, file_size IN SELECT f_name, f_size
                              FROM files
                              ORDER BY f_name
                              LOOP
      RAISE INFO 'The file % has a size of % bytes', file_name, file_size;
  END LOOP;
 END $code$;

There are various other ways of iterating over a result set by means of a FOR loop, such as by using cursors. We will look at these in more detail later on. We also have a FOREACH loop construct that iterates over array elements. The syntax is quite simple, since it only requires us to specify the iteration variable and the array over which to iterate. This is shown in Listing 25, where current_person is assigned a single name extracted from the people array at each iteration:

testdb=> DO $code$
 DECLARE
   pi CONSTANT real := 3.14;
   greeting_text CONSTANT text := 'Greetings ';
   people text[] := ARRAY[ 'Mr. Green', 'Mr. Red' ];
   current_person text;
 BEGIN
   FOREACH current_person IN ARRAY people LOOP
      RAISE INFO '% %', greeting_text, current_person;
      RAISE INFO 'Did you know that PI is %?', pi;
   END LOOP;
 END $code$;

INFO:  Greetings  Mr. Green
INFO:  Did you know that PI is 3.14?
INFO:  Greetings  Mr. Red
INFO:  Did you know that PI is 3.14?

Unlike the FOR loop, where the iteration variable can be declared in the loop control statement, the FOREACH loop requires the iteration variable to have already been declared in the code before the FOREACH statement is used.