CASE

Multi-way conditional (searched or simple form).

Syntax

CASE [<operand>]
  WHEN <expr> THEN <statements>
  [WHEN ...]
  [ELSE <statements>]
END CASE;

Description

## Overview The CASE statement provides multi-way branching in PL/pgSQL blocks. It supports two forms: the simple form (CASE operand WHEN value THEN ...) that compares a single expression against multiple values, and the searched form (CASE WHEN condition THEN ...) that evaluates independent boolean conditions. ## Behavior - In the simple form, the operand expression is evaluated once. Each WHEN clause value is then compared against it using equality. Multiple comma-separated values in a single WHEN clause are tested as alternatives (OR semantics). - In the searched form (no operand), each WHEN clause is a standalone boolean expression evaluated in order. - The first matching WHEN branch executes. Subsequent branches are skipped entirely. - If no WHEN branch matches and no ELSE clause is present, execution continues silently past the END CASE (no error is raised). - All expressions are evaluated via the DataFusion expression evaluator, so the full range of SQL operators and functions is available in conditions. ## Differences from PostgreSQL - PostgreSQL raises a CASE_NOT_FOUND error when no branch matches and ELSE is absent. DeltaForge does not raise this error; it silently falls through. Add an explicit ELSE clause to guarantee coverage. - Comparison in the simple form uses DataFusion equality, which follows SQL NULL semantics: NULL = NULL evaluates to NULL (not true). Use the searched form with IS NULL checks when NULL values are possible.

Examples

DO $$
DECLARE
  status TEXT := 'active';
BEGIN
  CASE status
    WHEN 'active' THEN PRINT 'Active';
    WHEN 'inactive' THEN PRINT 'Inactive';
    ELSE PRINT 'Unknown';
  END CASE;
END;
$$;

DO $$
DECLARE
  score INT := 85;
BEGIN
  CASE
    WHEN score >= 90 THEN PRINT 'Grade: A';
    WHEN score >= 80 THEN PRINT 'Grade: B';
    WHEN score >= 70 THEN PRINT 'Grade: C';
    ELSE PRINT 'Grade: F';
  END CASE;
END;
$$;

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT id, status FROM pipelines LOOP
    CASE rec.status
      WHEN 'running' THEN
        PRINT 'Pipeline', rec.id, 'is running';
      WHEN 'failed' THEN
        RAISE WARNING 'Pipeline % failed', rec.id;
      WHEN 'success' THEN
        NULL;
    END CASE;
  END LOOP;
END;
$$;

DO $$
DECLARE
  day_of_week INT;
BEGIN
  day_of_week := EXTRACT(DOW FROM CURRENT_DATE)::INT;
  CASE day_of_week
    WHEN 0, 6 THEN PRINT 'Weekend: skip pipeline';
    WHEN 1 THEN PRINT 'Monday: full refresh';
    ELSE PRINT 'Weekday: incremental load';
  END CASE;
END;
$$;

Pitfalls

• Silent fall-through occurs when no WHEN branch matches and no ELSE is provided. This differs from PostgreSQL, which raises CASE_NOT_FOUND. Always include an ELSE clause for safety.
• NULL comparisons in simple CASE never match because NULL = NULL is NULL, not true. Use the searched form with IS NULL for NULL-aware branching.
• CASE in PL/pgSQL is a statement (terminated by END CASE), not an expression. Use a SQL-level CASE expression inside SELECT or assignment for expression contexts.

See Also

• IF
• BLOCK