WHILE

Loop with a condition tested before each iteration.

Category: control-flow

Syntax

WHILE <condition> LOOP
  <statements>
END LOOP;

Description

## Overview The WHILE loop tests a boolean condition before each iteration. If the condition is true, the body executes; if false or NULL, the loop terminates. This provides "zero or more iterations" semantics, unlike the unconditional LOOP which always executes at least once. ## Behavior - Before each iteration (including the first), the condition expression is evaluated via the DataFusion expression evaluator. - Only `Boolean(Some(true))` continues the loop. Any other result (false, NULL, non-boolean) causes the loop to exit. - The loop body executes as a statement list. ControlFlow signals (EXIT, CONTINUE, RETURN) are handled identically to the unconditional LOOP construct. - EXIT with a matching label (or no label) breaks the WHILE loop. CONTINUE with a matching label skips to the next condition check. - The condition is re-evaluated from scratch on each iteration, so changes to variables during the body are reflected in the next check. ## Differences from PostgreSQL - Behavior is consistent with PostgreSQL PL/pgSQL. NULL conditions terminate the loop, matching standard three-valued logic.

Parameters

Name	Type	Description
`condition`		Provide a boolean expression evaluated before each iteration. The loop exits when the condition is false or NULL.

Examples

DO $$
DECLARE
  i INT := 1;
  total INT := 0;
BEGIN
  WHILE i <= 10 LOOP
    total := total + i;
    i := i + 1;
  END LOOP;
  PRINT 'Sum:', total;
END;
$$;

DO $$
DECLARE
  remaining INT;
BEGIN
  SELECT count(*) INTO remaining FROM work_queue WHERE status = 'pending';
  WHILE remaining > 0 LOOP
    EXECUTE 'UPDATE work_queue SET status = ''processing'' WHERE id = (SELECT id FROM work_queue WHERE status = ''pending'' LIMIT 1)';
    SELECT count(*) INTO remaining FROM work_queue WHERE status = 'pending';
    PRINT remaining, 'items remaining';
  END LOOP;
END;
$$;

DO $$
DECLARE
  val NUMERIC := 1024;
BEGIN
  WHILE val > 1 LOOP
    val := val / 2;
    PRINT val;
  END LOOP;
END;
$$;

DO $$
DECLARE
  attempts INT := 0;
  max_attempts INT := 3;
  done BOOLEAN := false;
BEGIN
  WHILE NOT done AND attempts < max_attempts LOOP
    attempts := attempts + 1;
    BEGIN
      PERFORM 1 FROM connectivity_check;
      done := true;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE NOTICE 'Attempt % failed', attempts;
    END;
  END LOOP;
END;
$$;

Pitfalls

A condition that never becomes false creates an infinite loop. Ensure the loop body modifies a variable or state that the condition depends on.
NULL conditions terminate the loop. If the condition expression can evaluate to NULL (e.g., comparing against a nullable column), the loop may exit earlier than expected. Use COALESCE to provide a default.
Unlike LOOP, WHILE may execute zero times if the condition is initially false. Verify that pre-loop initialization is correct.