Types, expressions, and control flow

​

Types

​

Literals

• Integer literals are typed by magnitude: a value that fits in 32 bits is INT, otherwise BIGINT. Beyond 64 bits, write it in decimal form (9300000000000000000.0). Scientific notation is not accepted.
• A literal with a decimal point is NUMERIC with the written scale (4.00 is NUMERIC scale 2).
• Strings are single-quoted: 'shipped'.
• Booleans are TRUE and FALSE.
• Dates and timestamps: DATE '2026-07-01', TIMESTAMP '2026-07-01 12:30:00'.
• A NULL that needs a type is written CAST(NULL AS T).

​

Variables and assignment

running := GREATEST(running + rec.delta, 0);
rec.status := 'priority';

​

Operators

tax := rec.subtotal * CASE WHEN rec.country = 'US' THEN 0.00
                           WHEN rec.country = 'DE' THEN 0.19
                           ELSE 0.10 END;

​

Numeric semantics

• Integers are width-checked at every + - * and every cast: INT math stays INT and faults with OVERFLOW past 32 bits; mixing widths promotes (INT * BIGINT is BIGINT).
• Decimals follow the engine’s strict typing: the result scale of + - * / is the widest operand scale, precision grows to fit and is capped at 38, and exceeding it is a loud overflow. Multiplication and division are computed in a wide intermediate and rounded half-up to that scale.
• Floats are IEEE 754 with no fused-multiply-add surprises. Mixing DOUBLE with NUMERIC or integers promotes to DOUBLE. A result that would be NaN or Infinity faults instead.
• Division by zero faults with ZERO_DIVIDE — never NULL, never Infinity.

​

NULL semantics

• NULL propagates through operators and functions: NULL + 1 is NULL, NULL || 'x' is NULL, comparisons with NULL are NULL.
• Test with IS NULL, or compare null-safely with IS [NOT] DISTINCT FROM.
• A condition that evaluates to NULL is not TRUE: an IF takes its ELSE path, and a capture filter skips the row.

​

Control flow

IF rec.balance < 0 THEN
  ...
ELSIF rec.flagged THEN
  ...
ELSE
  ...
END IF;

CASE rec.band              -- simple form: subject compared to each WHEN
  WHEN 3 THEN rec.grade := 'A';
  WHEN 2 THEN rec.grade := 'B';
  ELSE        rec.grade := 'C';
END CASE;

CASE                       -- searched form: first TRUE predicate wins
  WHEN rec.spend >= 100000 THEN rec.tier := 'platinum';
  ELSE                          rec.tier := 'standard';
END CASE;

WHILE i > 0 LOOP           -- bounded, in-memory
  s := s + i;
  i := i - 1;
END LOOP;

FOR i IN 1 .. rec.n LOOP   -- counted, ascending; i is a read-only INT
  ...
END LOOP;

• CONTINUE WHEN pred; (or bare CONTINUE;) skips to the next iteration.
• EXIT WHEN pred; (or bare EXIT;) leaves the innermost loop. Both also work at the batch-loop boundary itself — see stopping a batched read early.
• RETURN; ends the whole program cleanly (exit 0) from anywhere; writes persist only up to the last COMMIT that was reached.

• Inside a row loop (compute position): the body is in-memory compute — a read block or a COMMIT inside it is a compile error by design, so a per-row server operation stays unwriteable.
• At the top level of the program: the loop is a pipeline — its body takes the full top-level alphabet: whole read blocks, writes, lookups, and its own per-iteration COMMIT. See pipelines.

​

Records

DECLARE
  TYPE audit_rec IS RECORD (
    order_id    BIGINT,
    event       TEXT,
    amount      NUMERIC(14,2),
    logged_at   TIMESTAMP
  );
  ev  audit_rec;
  row2 order_audit%ROWTYPE;    -- shape = the table's columns, from the live catalog
BEGIN
  ev.order_id  := rec.order_id;
  ev.event     := 'shipped';
  ev.amount    := rec.amount;
  ev.logged_at := CURRENT_TIMESTAMP;

  INSERT INTO order_audit VALUES ev;   -- record shape must match the target row

​

Collections

DECLARE
  TYPE rate_map IS TABLE OF rate_dim%ROWTYPE INDEX BY tier;
  rmap rate_map;
BEGIN
  SELECT tier, mult BULK COLLECT INTO rmap FROM rate_dim WHERE mult > 0;

  FOR batch IN (SELECT id, amount, tier, adjusted FROM txns)
      ORDER KEY (id) BATCH SIZE 20000
  LOOP
    FOR rec IN batch LOOP
      rec.adjusted := rec.amount * rmap(rec.tier).mult;
      UPDATE txns SET ROW = rec WHERE id = rec.id;
    END LOOP;
    COMMIT;
  END LOOP;
END;

• INDEX BY names the key column(s) of the element type — composite keys are supported (INDEX BY wh, sku), and the probe key can be a runtime-computed value, the one correlation no JOIN can express.
• A probe on a missing key (or a NULL key) raises NO_DATA_FOUND — handleable like any fault.
• The map is a read-only lookup: there is no iteration over a collection, and no .COUNT-style methods. It is resident in memory, so load dimensions, not fact tables.
• Key columns may be integer, decimal, text, date, or timestamp — not float.

SELECT mult INTO v_mult FROM rate_dim WHERE tier = 'gold';

​

Identifiers and names

• Identifiers are ASCII: letters, digits, underscore, not starting with a digit. Case is not significant; the convention is UPPERCASE keywords and lowercase identifiers.
• Table names are single bare identifiers — there is no schema.table qualification. A table or column whose name needs quoting in the engine can be reached by aliasing it inside the read’s SQL.
• Reserved words cannot name variables, types, record fields, parameters, or procedures, and the check is case-insensitive. Three groups are reserved: every language keyword (including the type names, TRUE/FALSE, CURRENT_DATE/CURRENT_TIMESTAMP, STOP, the exception names, and the reserved-for-future words MERGE, FUNCTION, SCAN, FOLD, MAP, FILTER, COLLAPSE); the C++ keyword table, adopted wholesale and frozen (so class, template, new, … are rejected — the error says “reserved word” either way); and any name starting with the __sumatra_ prefix. The one deliberate non-reserved keyword is BATCH, so the canonical FOR batch IN (...) idiom can name its loop variable batch.