plpgsql-parser

plpgsql-parser

Combined SQL + PL/pgSQL parser with hydrated ASTs and transform API.

⚠️ Experimental: This package is currently experimental. If you're looking for just SQL parsing, see pgsql-parser. For body-only PL/pgSQL deparsing, see plpgsql-deparser.

Overview

This package provides a unified API for heterogeneous parsing and deparsing of SQL scripts containing PL/pgSQL functions. It handles the full pipeline: parsing SQL + PL/pgSQL together, transforming ASTs, and deparsing back to complete SQL.

Use this package when you need to:

• Parse and deparse complete CREATE FUNCTION statements with PL/pgSQL bodies
• Transform both SQL and embedded PL/pgSQL expressions (e.g., rename schemas)
• Round-trip SQL through parse → modify → deparse

Key features:

• Auto-detects CREATE FUNCTION statements with LANGUAGE plpgsql
• Hydrates PL/pgSQL function bodies into structured ASTs
• Automatic RETURN statement handling based on function return type
• Transform API for parse → modify → deparse workflows
• Re-exports underlying primitives for power users

Installation

npm install plpgsql-parser

Usage

import { parse, transform, deparseSync, loadModule } from 'plpgsql-parser';

// Initialize the WASM module
await loadModule();

// Parse SQL with PL/pgSQL functions - auto-detects and hydrates
const result = parse(`
  CREATE FUNCTION my_func(p_id int)
  RETURNS void
  LANGUAGE plpgsql
  AS $$
  BEGIN
    RAISE NOTICE 'Hello %', p_id;
  END;
  $$;
`);

console.log(result.functions.length); // 1
console.log(result.functions[0].plpgsql.hydrated); // Hydrated AST

// Transform API for parse -> modify -> deparse pipeline
const output = transformSync(sql, (ctx) => {
  // Modify the function name
  ctx.functions[0].stmt.funcname[0].String.sval = 'renamed_func';
});

// Deparse back to SQL
const sql = deparseSync(result, { pretty: true });

API

parse(sql, options?)

Parses SQL and auto-detects PL/pgSQL functions, hydrating their bodies.

Options:

• hydrate (default: true) - Whether to hydrate PL/pgSQL function bodies

Returns a ParsedScript with:

• sql - The raw SQL parse result
• items - Array of parsed items (statements and functions)
• functions - Array of detected PL/pgSQL functions with hydrated ASTs

transform(sql, callback, options?)

Async transform pipeline: parse -> modify -> deparse.

transformSync(sql, callback, options?)

Sync version of transform.

deparseSync(parsed, options?)

Converts a parsed script back to SQL.

Options:

• pretty (default: true) - Whether to pretty-print the output

Traverse API

The walkers themselves live in @pgsql/traverse and are re-exported here, so one import covers parsing and traversal. This package owns the one entry point that genuinely needs a parser: SQL text in.

walkSql(sql, visitors, options?)

Parses a SQL string, hydrates its PL/pgSQL function bodies, and walks both with the given visitors — SQL statements and PL/pgSQL bodies in a single pass.

import { loadModule, walkSql } from 'plpgsql-parser';

await loadModule();

const result = walkSql(sql, {
  // SQL nodes, at the top level and inside function bodies
  RangeVar: (path, ctx) => {
    if (ctx.isWrite && path.node.schemaname === 'audit') {
      ctx.abort('the audit schema is read-only');
    }
    if (ctx.insideFunction) {
      console.log(`${path.node.relname} referenced by ${ctx.functionName}`);
    }
  },
  // PL/pgSQL-only nodes, in the same visitor
  PLpgSQL_stmt_dynexecute: (_path, ctx) => ctx.abort('dynamic EXECUTE is not allowed')
});

result.aborted; // true when a visitor called ctx.abort()
result.reason;  // 'the audit schema is read-only'

Pass an array of visitors to compose independent policies in one parse. Every callback receives a WalkContext (stmtTag, stmtIndex, isWrite, isRead, insideFunction, functionName, abort) — see the @pgsql/traverse README for the full traversal reference.

Options:

• walkFunctionBodies (default: true) - Hydrate and walk PL/pgSQL function bodies. false skips the PL/pgSQL parse entirely
• walkSqlExpressions (default: true) - Recurse into hydrated SQL expressions inside bodies
• sqlVisitor - Override the visitor used for those SQL expressions

Unparseable input is reported as { aborted: true, reason } rather than throwing, so a validator can treat "rejected" and "could not be understood" uniformly.

walk(ast, visitors, options?)

Re-exported from @pgsql/traverse. Same behavior as walkSql, but takes an AST you already have — a ParsedScript from parse(), a ParseResult, a SQL node, or a PL/pgSQL node:

import { loadModule, parse, walk } from 'plpgsql-parser';

await loadModule();

const parsed = parse(`
  CREATE TABLE users (id int);
  CREATE FUNCTION get_user(id int) RETURNS text LANGUAGE plpgsql AS $$
  BEGIN
    RETURN (SELECT name FROM users WHERE users.id = id);
  END;
  $$;
`);

walk(parsed, {
  CreateStmt: () => console.log('CREATE TABLE statement'),
  RangeVar: (path) => console.log('Table reference:', path.node.relname),
  PLpgSQL_stmt_return: () => console.log('PL/pgSQL return statement')
});

Also re-exported: walkSqlAst (SQL-only primitive), walkPlpgsqlAst (PL/pgSQL-only primitive), PlpgsqlNodePath, and the WalkContext / UnifiedVisitor / WalkResult types.

Re-exports

For power users, the package re-exports underlying primitives:

• parseSql - SQL parser from @libpg-query/parser
• parsePlpgsqlBody - PL/pgSQL parser from @libpg-query/parser
• deparseSql - SQL deparser from pgsql-deparser
• deparsePlpgsqlBody - PL/pgSQL deparser from plpgsql-deparser
• hydratePlpgsqlAst - Hydration utility from plpgsql-deparser
• dehydratePlpgsqlAst - Dehydration utility from plpgsql-deparser
• walk, walkSqlAst, walkPlpgsqlAst - Walkers from @pgsql/traverse

License

MIT