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.

Overview

This package provides a deparser for PL/pgSQL (PostgreSQL's procedural language) AST structures. 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

The PL/pgSQL AST returned by parsePlPgSQL represents the internal structure of function bodies, not the CREATE FUNCTION statement itself. To get a complete function definition, you would need to:

• Parse the CREATE FUNCTION statement with the regular parse() function
• Extract the function body
• Parse the body with parsePlPgSQL()
• Deparse the body with this package
• Combine with the outer CREATE FUNCTION statement

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.