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

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:

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

Conditional index creation → `CREATE INDEX IF NOT EXISTS`

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

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