Variable Declaration

Declares a variable with type, optional NOT NULL, CONSTANT, DEFAULT, and COLLATION.

Category: declarations

Syntax

DECLARE
  <name> [CONSTANT] <type> [NOT NULL] [DEFAULT <expr>] [COLLATE '<collation>'];

Description

## Overview Variable declarations in the DECLARE section define typed, scoped variables for use within a PL/pgSQL block. Each variable has a name, a data type, and optional modifiers (CONSTANT, NOT NULL, DEFAULT, COLLATE). ## Behavior - Declarations are processed in order during block entry. Default expressions are evaluated sequentially, so later declarations can reference earlier ones. - Variables without a DEFAULT are initialized to NULL. If NOT NULL is specified without a DEFAULT, the first assignment must provide a non-NULL value before the variable is read. - CONSTANT variables are assigned once (from the DEFAULT expression) and cannot be reassigned. Attempting to assign to a CONSTANT raises a runtime error. - Data types map to Arrow/DataFusion types internally: INT to Int32, BIGINT to Int64, TEXT to Utf8, NUMERIC to Decimal128, BOOLEAN to Boolean, DATE to Date32, TIMESTAMP to TimestampMicrosecond, and arrays to List types. - RECORD variables are declared without a fixed schema. Their fields are populated dynamically when assigned from query results or explicit field assignments. - The COLLATE clause is parsed and stored but does not currently affect string comparison behavior in the DataFusion execution engine. - Variables are block-scoped. They are created when the block's DECLARE is processed and destroyed when the block exits (scope pop). ## Differences from PostgreSQL - `%TYPE` and `%ROWTYPE` are supported for type inference from table columns (e.g., `v_name table.column%TYPE`, `v_row schema.table%ROWTYPE`). - COLLATE is parsed but has no runtime effect. All string comparisons use the default DataFusion collation. - RECORD variables in DeltaForge use an IndexMap for field ordering, which preserves insertion order. Field access is by name (dot notation), consistent with PostgreSQL.

Parameters

Name	Type	Description
`name`		Assign a unique identifier for the variable within its block scope.
`data_type`		Specify the data type (INT, BIGINT, TEXT, NUMERIC, BOOLEAN, DATE, TIMESTAMP, arrays, RECORD, etc.).
`not_null`		Enforce a NOT NULL constraint. Assignment of NULL to this variable raises a runtime error.
`constant`		Mark the variable as read-only after its initial value is set. Subsequent assignments raise an error.
`default`		Set the initial value. Use DEFAULT or := syntax. Without a default, the variable is initialized to NULL.

Examples

DO $$
DECLARE
  counter INT := 0;
BEGIN
  counter := counter + 1;
  PRINT counter;
END;
$$;

DO $$
DECLARE
  user_name TEXT NOT NULL DEFAULT 'unknown';
  PI CONSTANT NUMERIC := 3.14159;
BEGIN
  PRINT user_name;
  PRINT PI;
END;
$$;

DO $$
DECLARE
  start_ts TIMESTAMP := now();
  batch_id BIGINT;
  is_active BOOLEAN := true;
  tags TEXT[] := ARRAY['etl', 'daily'];
BEGIN
  PRINT 'Started at:', start_ts;
  PRINT 'Active:', is_active;
  FOREACH tag IN ARRAY tags LOOP
    PRINT tag;
  END LOOP;
END;
$$;

DO $$
DECLARE
  rec RECORD;
  row_count INT;
BEGIN
  FOR rec IN SELECT id, name FROM employees LIMIT 5 LOOP
    PRINT rec.id, rec.name;
  END LOOP;
  GET DIAGNOSTICS row_count = ROW_COUNT;
  PRINT 'Processed', row_count, 'rows';
END;
$$;

DO $$
DECLARE
  threshold CONSTANT INT := 100;
  total INT := 0;
BEGIN
  SELECT sum(amount) INTO total FROM orders;
  IF total > threshold THEN
    PRINT 'Threshold exceeded:', total;
  END IF;
END;
$$;

Pitfalls

Variables without DEFAULT start as NULL. Reading a NOT NULL variable before its first assignment does not raise an error at declaration time; the error occurs on first read or use.
CONSTANT variables cannot be reassigned. Attempting `constant_var := new_value` raises a runtime error. Use a regular variable if the value may change.
The COLLATE clause is accepted syntactically but has no effect on runtime string comparisons. Do not rely on it for locale-sensitive sorting.
%TYPE and %ROWTYPE are supported for type inference from table columns (e.g., table.column%TYPE, schema.table%ROWTYPE).