FOREACH

Iterates over elements of an array.

Category: control-flow

Syntax

FOREACH <var> [SLICE <n>] IN ARRAY <array_expr> LOOP
  <statements>
END LOOP;

Description

## Overview FOREACH iterates over the elements of an array value, assigning each element (or slice) to the loop variable on every iteration. It is the array-specific counterpart to FOR...IN loops for queries and integer ranges. ## Behavior - The array expression is evaluated once before the loop begins. The result must be a LIST-type ScalarValue in the Arrow type system. - If the array is NULL or empty, the loop body never executes. - A new variable scope is pushed for the loop. If the loop variable already exists in an outer scope, the existing variable is reused (not shadowed), matching PostgreSQL behavior. - Each element is extracted from the Arrow ListArray and assigned to the loop variable. Type normalization (e.g., Utf8View to Utf8) is applied automatically. - For scalar element targets, the value is assigned directly. For record targets, struct-typed elements are decomposed into record fields; scalar elements are wrapped in a single-field record with key "value". - EXIT and CONTINUE work as expected within the loop body. ## Differences from PostgreSQL - SLICE support is parsed but has limited runtime support for multi-dimensional arrays. DeltaForge uses flat Arrow ListArrays internally, so deeply nested array slicing may not behave identically to PostgreSQL's multi-dimensional array model. - Array elements are Arrow ScalarValues. Some PostgreSQL array element types (e.g., composite types, domains) may not have direct Arrow equivalents.

Parameters

Name	Type	Description
`var`		Declare or reuse a variable to receive each array element on every iteration.
`array_expr`		Supply an expression that evaluates to an array (LIST type in Arrow).
`slice`		Set the slice dimension for multi-dimensional arrays. 0 iterates individual elements.

Examples

DO $$
DECLARE
  item INT;
  my_array INT[] := ARRAY[10, 20, 30, 40];
BEGIN
  FOREACH item IN ARRAY my_array LOOP
    PRINT item;
  END LOOP;
END;
$$;

DO $$
DECLARE
  tag TEXT;
  tags TEXT[] := ARRAY['etl', 'daily', 'production'];
BEGIN
  FOREACH tag IN ARRAY tags LOOP
    PRINT 'Processing tag:', tag;
  END LOOP;
END;
$$;

DO $$
DECLARE
  val INT;
  total INT := 0;
  numbers INT[] := ARRAY[5, 15, 25, 35, 45];
BEGIN
  FOREACH val IN ARRAY numbers LOOP
    total := total + val;
  END LOOP;
  PRINT 'Sum:', total;
END;
$$;

DO $$
DECLARE
  col_name TEXT;
  columns TEXT[];
BEGIN
  columns := ARRAY['id', 'name', 'created_at', 'updated_at'];
  FOREACH col_name IN ARRAY columns LOOP
    EXECUTE 'SELECT ' || col_name || ' FROM audit_log LIMIT 1';
  END LOOP;
END;
$$;

Pitfalls

A NULL array causes the loop to silently skip rather than raise an error. Validate the array before the loop if zero iterations indicate a logic error.
The SLICE parameter is parsed but multi-dimensional array slicing has limited support. Stick to flat (1-D) arrays for reliable behavior.
Modifying the source array variable inside the loop body does not affect iteration, because the array is evaluated once before the loop starts.