Procedural Language (PL)

Nano runs plain-SQL DO blocks and simple SQL/PL/pgSQL functions; PL/pgSQL control flow, trigger bodies, PL/SQL, T-SQL and DB2 SQL PL are planned. This page describes the procedural language surface, and marks what runs in the current release and what is planned.

What runs in the current release

• CREATE FUNCTION … LANGUAGE sql and LANGUAGE plpgsql functions whose bodies use SQL statements, SELECT INTO, RETURN, NULL, nested BEGIN … END and DECLARE without initialisers.
• DO blocks containing plain SQL statements, on the PostgreSQL simple-query path.
• CREATE PROCEDURE with plain SQL bodies, invoked with CALL.

Variables with initial values, := assignment, IF, CASE, LOOP, WHILE, FOR, RAISE, exception handlers, cursors and dynamic EXECUTE are refused in functions and DO blocks, and do not run correctly inside procedures. Trigger bodies are not executed, apart from BEFORE … FOR EACH ROW functions that only assign NEW.<col>. The examples in the sections below that use these constructs show the planned syntax. See PL/pgSQL compatibility for details.

Overview

The procedural language module enables:

• Stored Procedures: Encapsulate SQL statements behind CALL
• User-Defined Functions: Create reusable computations
• Control Flow (planned): IF/ELSE, LOOP, WHILE, FOR statements
• Variables: DECLARE and SELECT INTO today; := assignment and initial values are planned
• Exception Handling (planned): EXCEPTION blocks for error handling
• Cursors (planned): Iterate over query results

Supported Dialects

Dialect	Description	Primary Use
PL/pgSQL	PostgreSQL procedural language	Default; the subset listed above runs today
T-SQL	Microsoft SQL Server	Planned
PL/SQL	Oracle Database	Planned
DB2 SQL PL	IBM DB2	Planned
Auto	Auto-detect dialect	Planned

Basic Syntax

Anonymous Blocks

DO $$
BEGIN
    UPDATE users SET status = 'active' WHERE status IS NULL;
    DELETE FROM sessions WHERE expires_at < now();
END;
$$;

DO blocks run plain SQL statements only; DECLARE, RAISE and other PL/pgSQL control flow inside a DO block are planned.

CREATE FUNCTION

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM users;
    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE update_user_status(
    p_user_id INTEGER,
    p_status VARCHAR
)
AS $$
BEGIN
    UPDATE users SET status = p_status WHERE id = p_user_id;
END;
$$ LANGUAGE plpgsql;

Variables

Declaration

DECLARE
    v_name VARCHAR(100);           -- Simple variable
    v_count INTEGER := 0;          -- With default value
    v_active CONSTANT BOOLEAN := TRUE;  -- Constant
    v_user users%ROWTYPE;          -- Row type
    v_id users.id%TYPE;            -- Column type

Assignment

-- Direct assignment
v_count := 42;

-- SELECT INTO
SELECT name INTO v_name FROM users WHERE id = 1;

-- Multiple values
SELECT name, email INTO v_name, v_email FROM users WHERE id = 1;

Control Flow

Planned

The current release refuses the statements in this section inside functions and DO blocks; they show the planned syntax.

IF Statement

IF v_count > 100 THEN
    RAISE NOTICE 'High count';
ELSIF v_count > 50 THEN
    RAISE NOTICE 'Medium count';
ELSE
    RAISE NOTICE 'Low count';
END IF;

CASE Statement

CASE v_status
    WHEN 'active' THEN
        RETURN 1;
    WHEN 'inactive' THEN
        RETURN 0;
    ELSE
        RETURN -1;
END CASE;

LOOP

-- Basic loop
LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count >= 10;
END LOOP;

-- WHILE loop
WHILE v_count < 10 LOOP
    v_count := v_count + 1;
END LOOP;

-- FOR loop (numeric range)
FOR i IN 1..10 LOOP
    RAISE NOTICE 'Iteration: %', i;
END LOOP;

-- FOR loop (query result)
FOR rec IN SELECT * FROM users LOOP
    RAISE NOTICE 'User: %', rec.name;
END LOOP;

Exception Handling

Planned

The current release refuses the statements in this section inside functions and DO blocks; they show the planned syntax.

BEGIN
    -- Code that might fail
    INSERT INTO users (id, name) VALUES (1, 'Alice');
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'User already exists';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
END;

Common Exception Types

Exception	Description
unique_violation	Duplicate key
foreign_key_violation	FK constraint failed
null_value_not_allowed	NULL in NOT NULL column
check_violation	CHECK constraint failed
division_by_zero	Division by zero
OTHERS	Catch-all handler

Cursors

Planned

The current release refuses the statements in this section inside functions and DO blocks; they show the planned syntax.

DECLARE
    user_cursor CURSOR FOR SELECT * FROM users;
    user_record users%ROWTYPE;
BEGIN
    OPEN user_cursor;
    LOOP
        FETCH user_cursor INTO user_record;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'User: %', user_record.name;
    END LOOP;
    CLOSE user_cursor;
END;

Dialect-Specific Examples

Planned

T-SQL, PL/SQL and DB2 SQL PL routines are planned; the examples below show the planned syntax.

T-SQL Style

-- T-SQL uses @ for variables
DECLARE @v_count INT;
SET @v_count = 0;

WHILE @v_count < 10
BEGIN
    SET @v_count = @v_count + 1;
    PRINT @v_count;
END;

PL/SQL Style

-- PL/SQL uses := for assignment
DECLARE
    v_count NUMBER := 0;
BEGIN
    FOR i IN 1..10 LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE(v_count);
    END LOOP;
END;

Integration with Triggers

Planned

Trigger function bodies are not executed in the current release, apart from BEFORE … FOR EACH ROW functions that only assign NEW.<col> = <expr> and return NEW or NULL. Full trigger bodies are planned.

Procedural language functions are commonly used as trigger handlers:

-- Create trigger function
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to trigger
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

Built-in Functions

Utility Functions

Function	Description
RAISE NOTICE	Output debug message
RAISE EXCEPTION	Throw error
FOUND	Boolean - last query returned rows
ROW_COUNT	Rows affected by last statement
NOW()	Current timestamp

Trigger Context Variables

Variable	Description
TG_OP	Operation (INSERT, UPDATE, DELETE)
TG_TABLE_NAME	Name of trigger table
OLD	Row before modification
NEW	Row after modification
TG_WHEN	BEFORE or AFTER

Performance Considerations

• PL functions are compiled and cached
• Avoid excessive dynamic SQL
• Use bulk operations where possible
• Consider using IMMUTABLE/STABLE/VOLATILE appropriately

See Also

• Triggers
• SQL Reference
• Feature Index