PL/pgSQL Variables

Master PL/pgSQL variables — declaration syntax, common data types (INTEGER, NUMERIC, TEXT, BOOLEAN, DATE, TIMESTAMP, JSONB, arrays), NOT NULL constraints, := vs = assignment, and variable scope including shadowing. Includes the proper TIMESTAMP vs TIME usage for clock_timestamp() and PostgreSQL's 1-indexed arrays.

A variable is a named storage location declared in the DECLARE section of a block. It holds a single value of a specific type, optionally with an initial assignment, and can be read or written throughout the block's BEGIN ... END body.

Various types declared and assigned, with both := and DEFAULT shown:

DO $$
DECLARE
    counter      INTEGER       := 0;
    pi           NUMERIC(10,5) DEFAULT 3.14159;
    greeting     TEXT          := 'Hello, world';
    is_ready     BOOLEAN       := FALSE;
    today        DATE          := CURRENT_DATE;
    created_at   TIMESTAMP     := clock_timestamp();
BEGIN
    counter  := counter + 1;
    is_ready := TRUE;

    RAISE NOTICE 'counter:    %', counter;
    RAISE NOTICE 'pi:         %', pi;
    RAISE NOTICE 'greeting:   %', greeting;
    RAISE NOTICE 'is_ready:   %', is_ready;
    RAISE NOTICE 'today:      %', today;
    RAISE NOTICE 'created_at: %', created_at;
END $$;

Marking a variable NOT NULL is a runtime guarantee — assigning NULL to it later raises an error. Useful for working values that should never be undefined:

DO $$
DECLARE
    rate NUMERIC NOT NULL := 0.05;
BEGIN
    RAISE NOTICE 'rate = %', rate;

    rate := NULL;   -- This raises an error
    RAISE NOTICE 'after assignment: %', rate;
END $$;

Arrays are first-class types in PostgreSQL. Declare them with type[] and create them with ARRAY[...] literals or ARRAY(SELECT ...) subqueries:

DO $$
DECLARE
    fibonacci INTEGER[] := ARRAY[0, 1, 1, 2, 3, 5, 8, 13];
    cities    TEXT[]    := ARRAY['Chennai', 'Mumbai', 'Bengaluru'];
    total     INTEGER   := 0;
    i         INTEGER;
BEGIN
    -- Sum the array
    FOR i IN 1 .. array_length(fibonacci, 1) LOOP
        total := total + fibonacci[i];
    END LOOP;

    RAISE NOTICE 'Fibonacci:   %', fibonacci;
    RAISE NOTICE 'Sum:         %', total;
    RAISE NOTICE 'Array len:   %', array_length(fibonacci, 1);
    RAISE NOTICE 'First city:  %', cities[1];
    RAISE NOTICE 'All cities:  %', cities;
END $$;

Variables declared in an outer block are visible to nested blocks. If a nested block redeclares a variable with the same name, the inner declaration shadows the outer one — the outer variable becomes inaccessible inside the nested block, but resumes visibility when the nested block ends.

DO $$
DECLARE
    x INTEGER := 10;
BEGIN
    RAISE NOTICE 'Outer x = %', x;

    DECLARE
        x INTEGER := 100;       -- Shadows the outer x
    BEGIN
        RAISE NOTICE 'Inner x = %', x;
    END;

    RAISE NOTICE 'Outer x again = %', x;
END $$;

The outer x wasn't modified — it was shadowed for the duration of the inner block, then visible again afterwards.

PL/pgSQL has two assignment operators that mean the same thing — := and =. By convention, := is preferred because = is also the comparison operator inside expressions, and the visual difference helps reading.

DO $$
DECLARE
    a INTEGER;
    b INTEGER;
    fetched_salary NUMERIC;
BEGIN
    a := 10;            -- Preferred
    b  = 20;            -- Also valid; not recommended

    -- Assign from a query with SELECT INTO
    SELECT salary
    INTO   fetched_salary
    FROM   employees
    WHERE  first_name = 'Alice';

    RAISE NOTICE 'a = %, b = %, salary = %', a, b, fetched_salary;
END $$;

1. Declare every variable in DECLARE — Postgres requires it; you can't introduce new variables mid-block.
2. Initialize at declaration when sensible — saves a separate assignment line and rules out NULL by accident.
3. Use := for assignment, = for comparison — both work for assignment but the visual distinction helps.
4. Use NOT NULL for variables that should always have a value — but remember it requires an initializer.
5. Avoid shadowing. Different name = different concept; same name across nested blocks usually means a bug waiting.
6. Use %TYPE when the variable is tied to a column (see the data-types page).
7. Match types carefully — TIMESTAMP for timestamps (not TIME), NUMERIC(p,s) for decimals (not bare NUMERIC for money).

• Variables are declared in the DECLARE section with name [CONSTANT] type [NOT NULL] [:= initial];.
• Without an initializer, variables start as NULL.
• NOT NULL enforces non-null at runtime — and requires an initial value.
• Use := for assignment by convention; = also works but is the comparison operator in expressions.
• Variables in outer blocks are visible to nested blocks; nested redeclarations shadow them. Avoid this for code clarity.
• PostgreSQL arrays are 1-indexed; array_length(arr, 1) returns the count.