FOR (integer range)

Iterates over an integer range.

Category: control-flow

Syntax

FOR <var> IN [REVERSE] <lower>..<upper> [BY <step>] LOOP
  <statements>
END LOOP;

Description

## Overview The FOR integer range loop iterates a counter variable from a lower bound to an upper bound (inclusive), executing the loop body on each iteration. The REVERSE keyword reverses the direction, and the BY clause controls the step size. ## Behavior - The lower and upper bound expressions are evaluated once before the loop starts. Both are converted to INT64 internally. - A new variable scope is pushed. The loop variable is declared as INT64 within this scope, regardless of any outer declaration with the same name. - In normal (non-REVERSE) mode, the counter starts at the lower bound and increments by the step value until it exceeds the upper bound. - In REVERSE mode, the counter starts at the first number (lower in syntax, but the higher value) and decrements. The step is negated automatically, so `BY 2` in REVERSE mode decrements by 2. - A step of zero raises an immediate error ("FOR loop step cannot be zero"). - If the range is empty (e.g., `FOR i IN 10..1` without REVERSE), the loop body never executes. - EXIT and CONTINUE (with optional label) work within the loop body as expected. ## Differences from PostgreSQL - The loop variable is always INT64 in DeltaForge, regardless of prior declarations. PostgreSQL infers the loop variable type from the range bounds. - Bound expressions are evaluated as DataFusion expressions. Complex expressions (e.g., subqueries) are supported as long as they return a scalar integer.

Parameters

Name	Type	Description
`var`		Declare the loop counter variable. It is automatically created as INT64 in a new scope.
`lower`		Set the inclusive start of the range (or the upper bound when REVERSE is used).
`upper`		Set the inclusive end of the range (or the lower bound when REVERSE is used).
`step`		Specify the step increment. Defaults to 1. Must not be zero.
`reverse`		Enable reverse iteration. The loop counts down from the first bound to the second.

Examples

DO $$
BEGIN
  FOR i IN 1..10 LOOP
    PRINT i;
  END LOOP;
END;
$$;

DO $$
BEGIN
  FOR i IN REVERSE 10..1 BY 2 LOOP
    PRINT i;
  END LOOP;
END;
$$;

DO $$
DECLARE
  batch_size INT := 1000;
  total_rows INT;
BEGIN
  SELECT count(*) INTO total_rows FROM staging_data;
  FOR batch_num IN 0..total_rows / batch_size LOOP
    PRINT 'Processing batch', batch_num;
    EXECUTE 'INSERT INTO target SELECT * FROM staging_data LIMIT ' || batch_size || ' OFFSET ' || (batch_num * batch_size);
  END LOOP;
END;
$$;

DO $$
DECLARE
  factorial BIGINT := 1;
BEGIN
  FOR n IN 1..10 LOOP
    factorial := factorial * n;
  END LOOP;
  PRINT '10! =', factorial;
END;
$$;

Pitfalls

Forgetting REVERSE when iterating downward results in zero iterations. `FOR i IN 10..1 LOOP` never executes without REVERSE.
A step value of zero raises an immediate runtime error. Ensure step expressions cannot evaluate to zero.
The loop variable exists only within the loop scope. Accessing it after END LOOP raises an undefined-variable error. Copy it to an outer variable before exiting if you need the final value.