plpgsql-deparser

plpgsql-deparser

PL/pgSQL AST Deparser - Converts PL/pgSQL function ASTs back to SQL strings.

⚠️ Experimental: This package is currently experimental. If you're looking for SQL deparsing (not PL/pgSQL), see pgsql-deparser.

For full SQL + PL/pgSQL deparsing: If you need to deparse complete CREATE FUNCTION statements (not just function bodies), use plpgsql-parser instead. It handles the full heterogeneous parsing/deparsing pipeline automatically.

Overview

This package provides a body-only deparser for PL/pgSQL (PostgreSQL's procedural language) AST structures. It converts PL/pgSQL function bodies (the BEGIN...END part) back to strings. It works with the AST output from parsePlPgSQL function in @libpg-query/parser.

The PL/pgSQL AST is different from the regular SQL AST - it represents the internal structure of PL/pgSQL function bodies, including:

• Variable declarations (DECLARE section)
• Control flow statements (IF, CASE, LOOP, WHILE, FOR, FOREACH)
• Exception handling (BEGIN...EXCEPTION...END)
• Cursor operations (OPEN, FETCH, CLOSE)
• Return statements (RETURN, RETURN NEXT, RETURN QUERY)
• Dynamic SQL (EXECUTE)
• And more...

Installation

npm install plpgsql-deparser

Usage

Basic Usage

import { parsePlPgSQL } from '@libpg-query/parser';
import { deparse, PLpgSQLDeparser } from 'plpgsql-deparser';

// Parse a PL/pgSQL function
const funcSql = `
CREATE OR REPLACE FUNCTION test_func()
RETURNS INTEGER AS $$
DECLARE
  sum int := 0;
BEGIN
  FOR n IN 1..10 LOOP
    sum := sum + n;
  END LOOP;
  RETURN sum;
END;
$$ LANGUAGE plpgsql;
`;

const parseResult = await parsePlPgSQL(funcSql);

// Deparse the function body
const deparsed = await deparse(parseResult);
console.log(deparsed);

Synchronous Usage

import { deparseSync, PLpgSQLDeparser } from 'plpgsql-deparser';

const deparsed = deparseSync(parseResult);

With Options

import { PLpgSQLDeparser } from 'plpgsql-deparser';

const deparser = new PLpgSQLDeparser({
  indent: '    ',      // 4 spaces instead of default 2
  newline: '\n',       // newline character
  uppercase: false,    // lowercase keywords
});

const deparsed = deparser.deparseResult(parseResult);

Deparse a Single Function

import { PLpgSQLDeparser } from 'plpgsql-deparser';

// If you have just the function body AST
const funcBody = parseResult.plpgsql_funcs[0].PLpgSQL_function;
const deparsed = PLpgSQLDeparser.deparseFunction(funcBody);

Supported PL/pgSQL Constructs

Declarations

• Variable declarations with types, defaults, and constraints
• CONSTANT, NOT NULL modifiers
• RECORD types
• Cursor declarations

Control Flow

• IF / ELSIF / ELSE / END IF
• CASE (simple and searched)
• LOOP / END LOOP
• WHILE ... LOOP
• FOR i IN ... LOOP (integer range)
• FOR rec IN query LOOP
• FOR rec IN cursor LOOP
• FOREACH ... IN ARRAY

Exception Handling

• BEGIN ... EXCEPTION ... END blocks
• WHEN condition THEN handlers
• Multiple exception conditions

Cursor Operations

• OPEN cursor
• FETCH cursor INTO
• CLOSE cursor
• MOVE cursor

Return Statements

• RETURN expression
• RETURN NEXT
• RETURN QUERY
• RETURN QUERY EXECUTE

Other Statements

• Assignment (:=)
• RAISE (DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION)
• ASSERT
• PERFORM
• EXECUTE (dynamic SQL)
• GET DIAGNOSTICS
• COMMIT / ROLLBACK
• EXIT / CONTINUE

API Reference

deparse(parseResult, options?)

Async function to deparse a PL/pgSQL parse result.

deparseSync(parseResult, options?)

Synchronous version of deparse.

deparseFunction(func, options?)

Deparse a single PL/pgSQL function body.

deparseFunctionSync(func, options?)

Synchronous version of deparseFunction.

PLpgSQLDeparser

The main deparser class with full control over the deparsing process.

PLpgSQLDeparserOptions

interface PLpgSQLDeparserOptions {
  indent?: string;    // Indentation string (default: '  ')
  newline?: string;   // Newline character (default: '\n')
  uppercase?: boolean; // Uppercase keywords (default: true)
}

Note on AST Structure

This package deparses only the function body (the BEGIN...END part), not the full CREATE FUNCTION statement.

For full SQL + PL/pgSQL deparsing, use plpgsql-parser:

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

await loadModule();

const parsed = parse(`
  CREATE FUNCTION my_func() RETURNS void LANGUAGE plpgsql AS $$
  BEGIN
    RAISE NOTICE 'Hello';
  END;
  $$;
`);

// Full round-trip: parses SQL + PL/pgSQL, deparses back to complete SQL
const sql = deparseSync(parsed);

The plpgsql-parser package handles:

• Parsing the outer CREATE FUNCTION statement
• Hydrating embedded SQL expressions in the PL/pgSQL body
• Correct RETURN statement handling based on function return type
• Stitching the deparsed body back into the full SQL

License

MIT

🛠 Built by the Constructive team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on GitHub.

Related

• pgpm: A Postgres Package Manager that brings modular development to PostgreSQL with reusable packages, deterministic migrations, recursive dependency resolution, and tag-aware versioning.
• pgsql-test: Instant, isolated PostgreSQL databases for each test with automatic transaction rollbacks, context switching, and clean seeding for fast, reliable database testing.
• pgsql-seed: PostgreSQL seeding utilities for CSV, JSON, SQL data loading, and pgpm deployment.
• pgsql-parser: The real PostgreSQL parser for Node.js, providing symmetric parsing and deparsing of SQL statements with actual PostgreSQL parser integration.
• pgsql-deparser: A streamlined tool designed for converting PostgreSQL ASTs back into SQL queries, focusing solely on deparser functionality to complement pgsql-parser.
• @pgsql/parser: Multi-version PostgreSQL parser with dynamic version selection at runtime, supporting PostgreSQL 15, 16, and 17 in a single package.
• @pgsql/types: Offers TypeScript type definitions for PostgreSQL AST nodes, facilitating type-safe construction, analysis, and manipulation of ASTs.
• @pgsql/enums: Provides TypeScript enum definitions for PostgreSQL constants, enabling type-safe usage of PostgreSQL enums and constants in your applications.
• @pgsql/utils: A comprehensive utility library for PostgreSQL, offering type-safe AST node creation and enum value conversions, simplifying the construction and manipulation of PostgreSQL ASTs.
• @pgsql/traverse: PostgreSQL AST traversal utilities for pgsql-parser, providing a visitor pattern for traversing PostgreSQL Abstract Syntax Tree nodes, similar to Babel's traverse functionality but specifically designed for PostgreSQL AST structures.
• pg-proto-parser: A TypeScript tool that parses PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.
• libpg-query: The real PostgreSQL parser exposed for Node.js, used primarily in pgsql-parser for parsing and deparsing SQL queries.

Disclaimer

AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED "AS IS", AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.

No developer or entity involved in creating Software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the Software code or Software CLI, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.