7.6.7. LEAVE

Used for

Terminating a loop

Available in

PSQL

Syntax

[label:]
<loop_stmt>
BEGIN
  ...
  LEAVE [label];
  ...
END
<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list> DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>)} DO

A LEAVE statement immediately terminates the inner loop of a WHILE or FOR looping statement. The label parameter is optional.

LEAVE can cause an exit from outer loops as well. Code continues to be executed from the first statement after the termination of the outer loop block.

Examples

1. Leaving a loop if an error occurs on an insert into the NUMBERS table. The code continues to be executed from the line C = 0.

   ...
   WHILE (B < 10) DO
   BEGIN
     INSERT INTO NUMBERS(B)
     VALUES (:B);
     B = B + 1;
     WHEN ANY DO
     BEGIN
       EXECUTE PROCEDURE LOG_ERROR (
         CURRENT_TIMESTAMP,
         'ERROR IN B LOOP');
       LEAVE;
     END
   END
   C = 0;
   ...

2. An example using labels in the LEAVE statement. LEAVE LOOPA terminates the outer loop and LEAVE LOOPB terminates the inner loop. Note that the plain LEAVE statement would be enough to terminate the inner loop.

   ...
   STMT1 = 'SELECT NAME FROM FARMS';
   LOOPA:
   FOR EXECUTE STATEMENT :STMT1
   INTO :FARM DO
   BEGIN
     STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
     LOOPB:
     FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
     INTO :ANIMAL DO
     BEGIN
       IF (ANIMAL = 'FLUFFY') THEN
         LEAVE LOOPB;
       ELSE IF (ANIMAL = FARM) THEN
         LEAVE LOOPA;
       ELSE
         SUSPEND;
     END
   END
   ...

See also

EXIT