# plpgsql-parser

Combined SQL + PL/pgSQL parser with hydrated ASTs and transform API. > **Related packages:** For SQL-only parsing, see [`pgsql-parser`](https://www.npmjs.com/package/pgsql-parser). For body-only PL/pgSQL deparsing, see [`plpgsql-deparser`](https://www.npmjs.com/package/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 ```bash npm install plpgsql-parser ``` ## Usage ```typescript 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`](../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. ```typescript 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](../traverse) 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: ```typescript 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