Loop, exit, and continue statements

Syntax

The "loop" statement

Grammar
Diagram

plpgsql_loop_stmt ::= [ << label >> ] { plpgsql_unbounded_loop_defn
                                        | plpgsql_bounded_loop_defn } 
                      LOOP  [ plpgsql_executable_stmt [ , ... ] ]  
                      END LOOP [ label ]

plpgsql_unbounded_loop_defn ::= [ WHILE boolean_expression ]

plpgsql_bounded_loop_defn ::= plpgsql_integer_for_loop_defn
                              | plpgsql_array_foreach_loop_defn
                              | plpgsql_query_for_loop_defn

plpgsql_integer_for_loop_defn ::= FOR variable IN  [ REVERSE ] 
                                  int_expression .. int_expression  
                                  [ BY int_expression ]

plpgsql_array_foreach_loop_defn ::= FOREACH variable  
                                    [ SLICE int_literal ] IN ARRAY 
                                    array_expression

plpgsql_query_for_loop_defn ::= FOR variable [ variable [ , ... ] ] IN 
                                 { subquery
                                   | plpgsql_bound_refcursor_name
                                   | plpgsql_dynamic_subquery }

plpgsql_dynamic_subquery ::= EXECUTE text_expression 
                             [ USING expression [ , ... ] ]

plpgsql_loop_stmt

plpgsql_unbounded_loop_defn

plpgsql_bounded_loop_defn

plpgsql_integer_for_loop_defn

plpgsql_array_foreach_loop_defn

plpgsql_query_for_loop_defn

plpgsql_dynamic_subquery

The "exit" and "continue" statements

Grammar
Diagram

plpgsql_exit_stmt ::= EXIT [ label ] [ WHEN boolean_expression ]

plpgsql_continue_stmt ::= CONTINUE [ label ] 
                          [ WHEN boolean_expression ]

plpgsql_exit_stmt

plpgsql_continue_stmt

Semantics

There are two kinds of PL/pgSQL loop: the unbounded loop; and the (bounded) for loop.

The number of iterations that an unbounded loop performs isn't given by a simple recipe. Rather, iteration continues until it is interrupted, at any statement within its statement list, by invoking exit.
In contrast, the (maximum) number of iterations that a for loop performs is determined, before the first iteration starts. The recipe might, for example, be just the consecutive integers between a lower bound and an upper bound. Or it might be "for every element in the specified array", or "for every row in the result set of a specified query.

The functionality of all kinds of loops is complemented by the exit statement and the continue statement. The exit statement aborts the iteration altogether. And the continue statement aborts just the current iteration and then starts the next one.

See the section Two case studies: Using various kinds of "loop" statement, the "exit" statement, and the "continue" statement for realistic uses of all of the statements (except for the while loop) that this page describes.

Unbounded loop

The name unbounded denotes the fact that the number of iterations that the loop will complete is not announced at the start. Rather, iteration ends when facts that emerge while it executes determine that it's time to stop iterating. There are two kinds of unbounded loop: the infinite loop; and the while loop. See the dedicated page The "infinite loop" and the "while loop".

"Exit" statement

Most usually, the exit statement is written within a loop statement—but see the note immediately below.

The exit statement aborts the execution of the loop. Notice the optional label. It must match an end loop statement (or the end statement of a block statement) with the same label within the current top-level block statement.

When the label is omitted, the point of execution moves to the statement that immediately follows the present loop's end loop statement.
When exit some_label is used, the point of execution moves to the statement that immediately follows the end loop statement (or the bare end statement of the block statement) that has the same label.

An 'exit' statement's 'label' must match that of an 'end loop' statement or that of the 'end' statement of a block statement.

See the dedicated section Using the "exit" statement to jump out of a block statement.

"Continue" statement

The continue statement is legal only within a loop statement.

When label is omitted, the continue statement causes the current iteration to be abandoned and the next one to start immediately.
When label is specified, it causes the current iteration of the most tightly enclosing loop, and any loops in which it is nested through the one whose end loop matches the label, to be abandoned. Then the next iteration of the loop whose end loop matches the label starts immediately.
If used, the label must match that of an end loop statement.

It's possible to write the exit or the continue statement in one of the legs of an if statement or a case statement in the executable section, or even in the exception section, of a block statement at any nesting depth. Probably, in such a context, you'd omit the optional when clause.

Bounded loop

The name bounded denotes the fact that the (maximum) number of iterations that the loop will complete is computed, just before the first iteration, on entry into the loop. The qualifier "maximum" is used because the exit statement can be used to cause premature exit.

There are three kinds of bounded loop loop:

the integer for loop — defined by the plpgsql_integer_for_loop_defn syntax rule
the array foreach loop — defined by the plpgsql_array_foreach_loop_defn syntax rule
The query for loop — defined by the plpgsql_query_for_loop_defn syntax rule.