PostgreSQL Languages AST and statements prettifier

author:Lele Gaifax
license:GNU General Public License version 3 or later

This is 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 set of interconnected nodes, usually called an abstract syntax tree.

I needed a better SQL reformatter than the one implemented by sqlparse, and was annoyed by a few glitches (subselects in particular) that ruins the otherwise excellent job it does, considering that it is a generic library that tries to swallow many different SQL dialects.

When I found psqlparse I decided to try implementing a PostgreSQL focused tool: at the beginning it’s been easier than I feared, but I quickly hit some shortcomings in that implementation, so I opted for writing my own solution restarting from scratch, with the following goals:

  • target only Python 3.4+
  • target PostgreSQL 10 taking advantage of a work-in-progress branch of the libpg_query library
  • use a more dynamic approach to represent the parse tree, with a twofold advantage:
    1. it is much less boring to code, because there’s no need to write one Python class for each PostgreSQL node tag
    2. the representation is version agnostic, it can be adapted to newer/older Elephants in a snap
  • allow exploration of parse tree in both directions, because I realized that some kinds of nodes require that knowledge to determine their textual representation
  • avoid introducing arbitrary renames of tags and attributes, so what you read in PostgreSQL documentation/sources is available without the hassle of guessing how a symbol has been mapped
  • use a zero copy approach, keeping the original parse tree returned from the underlying libpg_query functions and have each node just borrow a reference to its own subtree

Indices and tables