PostgreSQL Languages AST and statements prettifier

7.13 · active · verified Sat Apr 11

A Python 3 module that exposes the parse tree of a PostgreSQL statement (extracted by the almost standard PG parser repackaged as a standalone static library by libpg_query) as a set of interconnected nodes, usually called an abstract syntax tree. It provides functionalities for parsing SQL, generating ASTs, and prettifying SQL statements. The library is actively maintained with frequent releases, currently at version 7.13, and targets PostgreSQL 17.

Warnings

breaking Since v3.0, `parse_sql()` returns native Python AST objects (subclasses of `pglast.ast.Node`) directly, not a JSON string. Code expecting JSON output or requiring manual JSON parsing will break.
Fix: Update code to directly interact with `pglast.ast` objects. Remove any JSON parsing logic after calling `parse_sql()`.
breaking The `pglast.node` wrapper classes were removed in v3.0. All AST manipulation now operates directly on the `pglast.ast` classes.
Fix: Replace usages of `pglast.node` wrappers with direct interaction with `pglast.ast` classes.
breaking The `pglast.printer` module was removed in v3.0. Print-specific functionalities are now in the `pglast.printers` subpackage, and serialization classes are in the new `pglast.stream` module.
Fix: Adjust import paths and function calls to use `pglast.printers` and `pglast.stream` as appropriate.
breaking In v5.0, the type of `pglast.ast.Float` values changed from `Decimal` to `str`. This was to resolve issues with floating-point representation and rendering.
Fix: Ensure that code handling `Float` node values expects a string and performs explicit conversion if numerical operations are required.
breaking Version 7.0 introduced a requirement for Python >= 3.9 and explicitly targets PostgreSQL 17. Older Python versions or applications targeting older PostgreSQL versions might encounter compatibility issues.
Fix: Upgrade Python environment to 3.9 or higher. Be aware of potential syntax changes or new features in PostgreSQL 17 that might affect parsing or prettification if interacting with older database versions.
gotcha The `parse_plpgsql()` function has known limitations and may not fully parse complex PL/pgSQL bodies, often treating the body as a single string literal within the AST. The maintainer has noted that its support is 'still vaporware'.
Fix: For complex PL/pgSQL, be aware that the internal body might not be fully parsed into a detailed AST. You might need to extract the function body as a string and process it with external tools or custom logic.

Install

pip install pglast Install stable version

Imports

parse_sql
```
from pglast import parse_sql
```
prettify
```
from pglast import prettify
```
ast
```
from pglast import ast
```
Provides the Python classes representing PostgreSQL AST nodes (e.g., ast.SelectStmt).

Quickstart

Parses an SQL statement into an Abstract Syntax Tree (AST) and then demonstrates how to traverse the AST for basic inspection, followed by prettifying the original SQL statement.

from pglast import parse_sql, prettify
from pglast.ast import SelectStmt, A_Const, Integer

sql_statement = "SELECT 1 + 2 AS sum_result, 'hello' WHERE a = B;"
ast_tree = parse_sql(sql_statement)

# The root of the AST is a tuple of RawStmt objects
root_stmt = ast_tree[0].stmt

# Example of inspecting the AST
if isinstance(root_stmt, SelectStmt):
    print(f"Parsed a SelectStmt.")
    for target in root_stmt.targetList:
        print(f"  Target: {target.name or ''} value type: {type(target.val)}")
        if isinstance(target.val, A_Const) and isinstance(target.val.val, Integer):
            print(f"    Integer constant: {target.val.val.val}")

# Prettify the SQL statement
pretty_sql = prettify(sql_statement)
print("\nOriginal SQL:")
print(sql_statement)
print("\nPrettified SQL:")
print(pretty_sql)

view raw JSON →