---
title: "PL/pgSQL compatibility"
---

# PL/pgSQL compatibility

HeliosDB Nano accepts `DO $$ … $$` / `DO LANGUAGE plpgsql $tag$ … $tag$` blocks and executes **plain SQL** statement bodies. Nano runs plain-SQL DO blocks and simple SQL/PL/pgSQL functions; PL/pgSQL control flow (variables, `FOR … IN SELECT … LOOP`, `IF`/`ELSE`, `RAISE`) and trigger bodies are planned. See [Functions, procedures and triggers](#functions-procedures-and-triggers) for the scope outside DO blocks.

When a DO block contains PL/pgSQL syntax, the server returns a clear error — it does **not** silently no-op, which would corrupt migrations that rely on the block running.

## Supported

- `DO $$ BEGIN <plain SQL>; <plain SQL>; END $$;`
- `DO $tag$ BEGIN <plain SQL>; END $tag$;`
- `DO LANGUAGE plpgsql $$ <plain SQL> $$;`

Bodies may contain `CREATE`, `ALTER`, `DROP`, `INSERT`, `UPDATE`, `DELETE`, and `SELECT` statements separated by `;`. Each runs as its own implicit transaction. An `EXCEPTION WHEN <condition> THEN NULL` handler that swallows an error is honoured.

DO blocks are handled on the PostgreSQL simple-query path (for example `psql` or a driver's simple `query()` call). The extended query protocol and the embedded `execute()` API do not run DO blocks.

## Not supported

- `DECLARE <name> <type>;` — variables
- `FOR <var> IN SELECT … LOOP` / `FOR <i> IN 1..n LOOP` — loops
- `IF <cond> THEN … ELSIF … END IF;` — conditionals
- `WHILE <cond> LOOP … END LOOP;`
- `RAISE NOTICE | EXCEPTION | WARNING …;`
- `RETURN`, `PERFORM`, `EXIT`, `CONTINUE`
- `EXCEPTION WHEN … THEN <statements> END;` — error handlers other than `THEN NULL`
- Variable assignment (`<var>:= <expr>`)
- Cursors (`DECLARE cur CURSOR FOR …; FETCH …`)

## Error message

When HeliosDB Nano detects PL/pgSQL control-flow tokens in a DO block body, it returns:

```
ERROR:  PL/pgSQL control flow (`<KEYWORD>`) inside DO blocks is not yet
        supported in HeliosDB Nano. Rewrite the block as plain SQL, or
        execute each statement separately.
        See: docs/compatibility/plpgsql.md
```

`<KEYWORD>` is the first control-flow token we spotted, chosen to help you locate the offending line.

## Migration patterns

### Backfill loop → plain SQL `UPDATE … FROM`

Common Drizzle / Prisma migration:

```sql
DO $$
DECLARE u RECORD;
BEGIN
  FOR u IN SELECT id, email FROM users LOOP
    INSERT INTO user_profile (user_id, display_name)
    VALUES (u.id, u.email);
  END LOOP;
END $$;
```

Rewrite as a single `INSERT … SELECT`:

```sql
INSERT INTO user_profile (user_id, display_name)
SELECT id, email FROM users;
```

### Conditional index creation → `CREATE INDEX IF NOT EXISTS`

```sql
-- Not supported
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'users_email_idx') THEN
    CREATE INDEX users_email_idx ON users(email);
  END IF;
END $$;

-- Use instead
CREATE INDEX IF NOT EXISTS users_email_idx ON users(email);
```

### Conditional data load → `INSERT … ON CONFLICT DO NOTHING`

```sql
-- Not supported
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM tenants WHERE id = 'default') THEN
    INSERT INTO tenants (id, name) VALUES ('default', 'Default Tenant');
  END IF;
END $$;

-- Use instead
INSERT INTO tenants (id, name) VALUES ('default', 'Default Tenant')
ON CONFLICT (id) DO NOTHING;
```

## Functions, procedures and triggers

- **`CREATE FUNCTION … LANGUAGE plpgsql`.** Function bodies may contain SQL
  statements, `SELECT INTO`, `RETURN`, `NULL`, nested `BEGIN … END` blocks and
  `DECLARE` without initialisers. `IF`, `CASE`, `LOOP`, `WHILE`, `FOR`, `:=`,
  `RAISE`, `EXIT`, `CONTINUE`, cursors, dynamic `EXECUTE`, `RETURN NEXT` /
  `RETURN QUERY`, `EXCEPTION` handlers and `CALL` are refused when the function is
  created. `LANGUAGE sql` functions are supported. Reference parameters with the `$`
  sigil (`$1` or `$name`); a bare parameter name is read as a column reference.
- **`CREATE PROCEDURE`.** Procedure bodies are not checked for control flow when
  they are created, and control flow inside them does not run correctly (an
  `IF` takes the `ELSE` branch, a `WHILE` loop does not iterate). Keep procedure
  bodies to plain SQL statements.
- **Triggers.** `CREATE TRIGGER` is accepted, but trigger function bodies are
  not executed. The one exception is a `BEFORE … FOR EACH ROW` function whose
  body only assigns `NEW.<col> = <expr>` and returns `NEW` or `NULL`. `AFTER`
  triggers and statement-level triggers are accepted and do nothing. Do not rely
  on triggers for audit rows or derived data.

PL/pgSQL control flow in functions, procedures and DO blocks, and trigger
function bodies, are planned.

## Roadmap

A minimal PL/pgSQL interpreter is tracked as a follow-up. Priority depends on customer demand — if you hit a real migration that doesn't fit the patterns above, open an issue with the block and we'll either adjust the rewrite recipes or fast-track the interpreter.