---
title: "Procedural Language (PL)"
---

# 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.

:::caution[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](https://www.heliosdb.com/docs/nano/reference/compatibility/plpgsql/) 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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
-- 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

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

### IF Statement

```sql
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

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

### LOOP

```sql
-- 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

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

```sql
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

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

```sql
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

:::caution[Planned]
T-SQL, PL/SQL and DB2 SQL PL routines are planned; the examples below show the
planned syntax.
:::

### T-SQL Style

```sql
-- 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

```sql
-- 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

:::caution[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:

```sql
-- 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](https://www.heliosdb.com/docs/nano/features/triggers/)
- [SQL Reference](https://www.heliosdb.com/docs/nano/user-guide/sql-reference/)
- [Feature Index](https://www.heliosdb.com/docs/nano/developer/feature-index/)