# 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