LOOP

Unconditional loop -- must use EXIT to terminate.

Category: control-flow

Syntax

[<<label>>]
LOOP
  <statements>
  EXIT [WHEN <condition>];
END LOOP;

Description

## Overview The unconditional LOOP repeats its body indefinitely until an EXIT statement breaks out. It is the simplest loop construct and serves as the foundation for custom iteration patterns that do not fit FOR or WHILE semantics. ## Behavior - The loop body executes repeatedly. After each iteration, the executor checks the returned ControlFlow signal. - ControlFlow::Continue means the loop body completed normally; the next iteration begins immediately. - ControlFlow::Exit with a matching label (or no label) breaks the loop. If the label does not match, the Exit signal propagates to the enclosing loop. - ControlFlow::ContinueLoop with a matching label skips the rest of the current iteration and starts the next one. A non-matching label propagates outward. - ControlFlow::Return immediately terminates the loop and the enclosing block/function. - Without any EXIT statement, the loop runs forever. There is no built-in iteration limit or timeout guard. ## Differences from PostgreSQL - Behavior is consistent with PostgreSQL PL/pgSQL. The loop runs indefinitely until EXIT or RETURN.

Examples

DO $$
DECLARE
  counter INT := 0;
BEGIN
  <<counter_loop>>
  LOOP
    counter := counter + 1;
    EXIT counter_loop WHEN counter >= 10;
  END LOOP;
  PRINT 'Counted to', counter;
END;
$$;

DO $$
DECLARE
  rec RECORD;
  found BOOLEAN := false;
BEGIN
  LOOP
    SELECT id, status INTO rec FROM job_queue WHERE status = 'pending' LIMIT 1;
    EXIT WHEN rec IS NULL;
    PRINT 'Processing job', rec.id;
    UPDATE job_queue SET status = 'done' WHERE id = rec.id;
  END LOOP;
  PRINT 'All jobs processed';
END;
$$;

DO $$
DECLARE
  retries INT := 0;
  max_retries INT := 5;
  success BOOLEAN := false;
BEGIN
  LOOP
    retries := retries + 1;
    BEGIN
      EXECUTE 'INSERT INTO target SELECT * FROM staging';
      success := true;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE NOTICE 'Attempt % failed: %', retries, SQLERRM;
    END;
    EXIT WHEN success OR retries >= max_retries;
  END LOOP;
  IF NOT success THEN
    RAISE EXCEPTION 'Failed after % retries', max_retries;
  END IF;
END;
$$;

DO $$
DECLARE
  batch_start INT := 0;
  batch_size INT := 500;
  rows_affected INT;
BEGIN
  LOOP
    DELETE FROM expired_sessions WHERE id IN (
      SELECT id FROM expired_sessions LIMIT batch_size
    );
    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    EXIT WHEN rows_affected = 0;
    batch_start := batch_start + rows_affected;
    PRINT 'Deleted', batch_start, 'rows so far';
  END LOOP;
END;
$$;

Pitfalls

An infinite loop results if no EXIT or RETURN is reachable. Always ensure the loop has a clear termination condition.
Placing EXIT at the top of the loop body creates "check before execute" semantics; placing it at the bottom creates "execute then check" semantics. Choose placement deliberately.
An unlabeled EXIT inside nested loops only breaks the innermost loop. Use labels to break outer loops from an inner context.

LOOP

Syntax

Description

Examples

Pitfalls

See Also