pglast

https://pglast.readthedocs.io/en/v7/
Admin
pglast is a Python 3 module that exposes the Abstract Syntax Tree (AST) of PostgreSQL statements,...
Tokens:172,925
Snippets:856
Trust Score:8.3
Update:1 year ago
Show doc for...
Tokens:
Raw
### Install pglast Library

Source: https://pglast.readthedocs.io/en/v7/_sources/installation.rst

Commands to install the pglast library, either directly via pip from PyPI or by cloning the source repository from GitHub and installing it locally.

```Shell
pip install pglast
```

```Shell
git clone https://github.com/lelit/pglast.git --recursive
```

```Shell
pip install ./pglast
```

--------------------------------

### Get Compact SQL Representation with pgpp Command-Line Tool

Source: https://pglast.readthedocs.io/en/v7/usage

This example demonstrates how to use the `pgpp` command-line tool's `--compact` option to control the maximum line length for SQL output. It shows how different `--compact` values affect the formatting, allowing users to generate more compact or more readable SQL representations based on their preferences.

```bash
pgpp --compact 30 -S "select a,b,c from st where a='longvalue1' and b='longvalue2'"
```

```bash
pgpp --compact 60 -S "select a,b,c from st where a='longvalue1' and b='longvalue2'"
```

--------------------------------

### Install pglast from local source

Source: https://pglast.readthedocs.io/en/v7/installation

Installs the pglast library from a local directory, typically after cloning the repository. This command uses pip to install the package from the specified path.

```bash
$ pip install ./pglast
```

--------------------------------

### Install pglast with pip

Source: https://pglast.readthedocs.io/en/v7/installation

Installs the pglast library globally using the Python package installer, pip. This is the recommended and easiest method for installation.

```bash
$ pip install pglast
```

--------------------------------

### Create a New AST Node

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Explains how to instantiate a new AST node, such as `SelectStmt`, and optionally pass recognized attributes as parameters to its constructor for initial configuration.

```python
print(ast.SelectStmt())
print(ast.SelectStmt(all=1))
```

--------------------------------

### Clone pglast Git repository

Source: https://pglast.readthedocs.io/en/v7/installation

Clones the pglast source code repository from GitHub. The `--recursive` flag ensures that all necessary submodules are also downloaded.

```bash
$ git clone https://github.com/lelit/pglast.git --recursive
```

--------------------------------

### Trace pglast AST Traversal with NoisyVisitor

Source: https://pglast.readthedocs.io/en/v7/usage

This example illustrates creating a custom `Visitor` to print the AST structure during traversal. The `visit` method is overridden to display the `ancestors` path and a concise representation of the current `node`, offering a detailed trace of how the visitor navigates the AST's hierarchical structure.

```python
class NoisyVisitor(Visitor):
    def visit(self, ancestors, node):
        print(ancestors, ':', node(depth=0, skip_none=True))

visitor = NoisyVisitor()
visitor(parse_sql('select a, b from c'))
```

--------------------------------

### Pglast SQL Statement Reformatting with `prettify`

Source: https://pglast.readthedocs.io/en/v7/usage

Illustrates the use of the `pglast.prettify` function to reformat SQL statements for improved readability. It shows examples with various SQL queries and demonstrates how to use the `comma_at_eoln` option to control comma placement.

```python
>>> from pglast import prettify
>>> print(prettify('delete from sometable where value is null'))
DELETE FROM sometable
WHERE value IS NULL

>>> print(prettify('select a,b,c from sometable where value is null'))
SELECT a
     , b
     , c
FROM sometable
WHERE value IS NULL

>>> print(prettify('select a,b,c from sometable'
...                ' where value is null or value = 1',
...                comma_at_eoln=True))
SELECT a,
       b,
       c
FROM sometable
WHERE value IS NULL
   OR value = 1
```

--------------------------------

### Reformat SQL Statements using pgpp CLI

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates various SQL reformatting capabilities of the `pgpp` command-line tool. Examples include basic formatting, controlling comma placement, handling complex `CASE` statements, updating `NULL` values, and splitting long string literals based on a specified column width.

```Shell
# Basic SQL reformatting
echo "select a,b,c from sometable" | pgpp
# Expected output:
# SELECT a
#      , b
#      , c
# FROM sometable

# Reformat with commas at end-of-line
echo "select a,b,c from sometable" | pgpp --comma-at-eoln
# Expected output:
# SELECT a,
#        b,
#        c
# FROM sometable

# Reformat a complex SQL statement with -S (string input)
pgpp -S "select a, case when a=1 then 'singular' else 'plural' end from test"
# Expected output:
# SELECT a
#      , CASE
#          WHEN (a = 1)
#            THEN 'singular'
#          ELSE 'plural'
#        END
# FROM test

# Reformat an UPDATE statement
echo 'update "table" set value=123 where value is null' | pgpp
# Expected output:
# UPDATE "table"
# SET value = 123
# WHERE value IS NULL

# Reformat with string splitting (-s option)
echo "
insert into t (id, description)
values (1, 'this is short enough'),
       (2, 'this is too long, and will be splitted')" | pgpp -s 20
# Expected output:
# INSERT INTO t (id, description)
# VALUES (1, 'this is short enough')
#      , (2, 'this is too long, an'
#            'd will be splitted')
```

--------------------------------

### Compact SQL Representation with pgpp CLI

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Shows how to obtain more compact SQL representations using the `pgpp` command-line tool's `--compact` option. This option attempts to fit more elements on a single line, useful for reducing vertical space.

```Shell
# Compact output with a column limit of 30
pgpp --compact 30 -S "select a,b,c from st where a='longvalue1' and b='longvalue2'"
# Expected output:
# SELECT a, b, c
# FROM st
# WHERE (a = 'longvalue1')
#   AND (b = 'longvalue2')

# More compact output with a column limit of 60
pgpp --compact 60 -S "select a,b,c from st where a='longvalue1' and b='longvalue2'"
# Expected output:
# SELECT a, b, c
# FROM st
# WHERE (a = 'longvalue1') AND (b = 'longvalue2')
```

--------------------------------

### Parse SQL Statement to AST Root Node

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates how to parse an SQL statement using `pglast.parser.parse_sql` to obtain a tuple of `pglast.ast.RawStmt` instances, representing the Abstract Syntax Tree (AST) root.

```python
from pglast import parse_sql
root = parse_sql('select 1')
print(root)
```

--------------------------------

### Pglast AST Node Creation and Attribute Handling

Source: https://pglast.readthedocs.io/en/v7/usage

Demonstrates two methods for creating new AST nodes in pglast: direct instantiation with attributes passed as constructor parameters, and instantiation using a dictionary with a special '@' key for the node name. It also illustrates error handling for invalid or non-existent attributes and how to clone/reconstruct AST nodes.

```python
>>> print(ast.SelectStmt())
<SelectStmt>
>>> print(ast.SelectStmt(all=1))
<SelectStmt all=True>
>>> ast.SelectStmt(non_existing_attribute=None)
Traceback (most recent call last):
  ...
TypeError: __init__() got an unexpected keyword argument 'non_existing_attribute'
>>> ast.SelectStmt(all="foo")
Traceback (most recent call last):
  ...
ValueError: Bad value for attribute SelectStmt.all, expected (<class 'bool'>, <class 'int'>), got <class 'str'>: 'foo'

>>> print(ast.SelectStmt({'@': 'SelectStmt', 'all': True}))
<SelectStmt all=True>
>>> print(ast.SelectStmt({'@': 'RawStmt', 'all': True}))
Traceback (most recent call last):
  ...
ValueError: Bad argument, wrong "@" value, expected 'SelectStmt', got 'RawStmt'

>>> clone = ast.SelectStmt(stmt())
>>> clone is stmt
False
>>> clone == stmt
True
```

--------------------------------

### Compare pglast AST nodes for equality

Source: https://pglast.readthedocs.io/en/v7/usage

This example demonstrates comparing two `pglast` AST nodes for equality. Nodes are considered equal if all their semantically relevant attributes match, ignoring differences like comments or formatting in the original SQL.

```Python
other_stmt = parse_sql('select /* something here */ 1')[0].stmt
print(other_stmt(depth=2, skip_none=True))
{'@': 'SelectStmt', 'targetList': ({'@': 'ResTarget', 'val': …, 'location': 28},), ...}
stmt == other_stmt
True
```

--------------------------------

### Direct SQL Formatting with pglast.stream.IndentedStream and RawStream

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Explains that `prettify` is a wrapper around `IndentedStream` and demonstrates direct usage of `IndentedStream` for SQL formatting, including the `comma_at_eoln` option. It also shows how `RawStream` can be used to print specific parts of an AST node, such as a `fromClause`.

```Python
from pglast.stream import IndentedStream, RawStream
print(IndentedStream(comma_at_eoln=True)('select a,b,c from sometable'))
SELECT a,
       b,
       c
FROM sometable
print(IndentedStream()('select foo from bar'))
SELECT foo
FROM bar
sql = 'select a.x, b.y from a join b on a.bid = b.id'
astnode = parse_sql(sql)[0].stmt
astnode
<SelectStmt targetList=(<ResTarget val=<ColumnRef fields=(<String sval='a'>, <String sval='x'>)>>...
print(RawStream()(astnode.fromClause))
a INNER JOIN b ON a.bid = b.id
```

--------------------------------

### Instantiating pglast AST Nodes and Handling Errors

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates how to create Abstract Syntax Tree (AST) nodes using `pglast.ast` and the types of errors (TypeError, ValueError) that can occur with incorrect arguments or values. It also shows how to instantiate nodes from a dictionary with the special `@` key for dynamic node creation.

```Python
ast.SelectStmt(non_existing_attribute=None)
Traceback (most recent call last):
  ...
TypeError: __init__() got an unexpected keyword argument 'non_existing_attribute'
ast.SelectStmt(all="foo")
Traceback (most recent call last):
  ...
ValueError: Bad value for attribute SelectStmt.all, expected (<class 'bool'>, <class 'int'>), got <class 'str'>: 'foo'
print(ast.SelectStmt({'@': 'SelectStmt', 'all': True}))
<SelectStmt all=True>
print(ast.SelectStmt({'@': 'RawStmt', 'all': True}))
Traceback (most recent call last):
  ...
ValueError: Bad argument, wrong "@" value, expected 'SelectStmt', got 'RawStmt'
```

--------------------------------

### Modifying AST: Dropping NULL Constraints with pglast Visitor

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates how to modify an AST by using a custom `pglast.visitors.Visitor` to remove specific nodes. This example shows how to drop `NULL` constraints from a `CREATE TABLE` statement by returning `Delete` from the `visit_Constraint` method.

```Python
from pglast import enums
from pglast.visitors import Delete

class DropNullConstraint(Visitor):
    def visit_Constraint(self, ancestors, node):
        if node.contype == enums.ConstrType.CONSTR_NULL:
            return Delete

raw = parse_sql('create table foo (a integer null, b integer not null)')
DropNullConstraint()(raw)
(<RawStmt stmt=<CreateStmt ...
print(RawStream()(raw))
CREATE TABLE foo (a integer, b integer NOT NULL)
```

--------------------------------

### Normalize SQL Representation with pgpp CLI

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates how to obtain an even more compact and normalized SQL representation using the `pgpp` command-line tool's `--normalize` option. This option removes unnecessary whitespace and formats the SQL into a single line.

```Shell
# Normalize SQL statement
pgpp --normalize -S "select a,b,c from st    where a='foo'"
# Expected output:
# SELECT a, b, c FROM st WHERE a = 'foo'
```

--------------------------------

### Debugging AST Traversal with a Noisy pglast Visitor

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Illustrates how to create a `pglast.visitors.Visitor` that prints the current node and its ancestors during AST traversal. This is useful for debugging and understanding the structure of the AST.

```Python
class NoisyVisitor(Visitor):
    def visit(self, ancestors, node):
        print(ancestors, ':', node(depth=0, skip_none=True))

visitor = NoisyVisitor()
visitor(parse_sql('select a, b from c'))
ROOT → 0 : {'@': 'RawStmt', 'stmt': …, 'stmt_location': 0, 'stmt_len': 0}
ROOT → 0 → stmt : {'@': 'SelectStmt', 'targetList': …, 'fromClause': …, ...
ROOT → 0 → stmt → targetList → 0 : {'@': 'ResTarget', 'val': …, 'location': 7}
ROOT → 0 → stmt → targetList → 1 : {'@': 'ResTarget', 'val': …, 'location': 10}
ROOT → 0 → stmt → fromClause → 0 : {'@': 'RangeVar', 'relname': 'c', 'inh': True, ...
ROOT → 0 → stmt → targetList → 0 → val : {'@': 'ColumnRef', 'fields': …, 'location': 7}
ROOT → 0 → stmt → targetList → 1 → val : {'@': 'ColumnRef', 'fields': …, 'location': 10}
ROOT → 0 → stmt → targetList → 0 → val → fields → 0 : {'@': 'String', 'sval': 'a'}
ROOT → 0 → stmt → targetList → 1 → val → fields → 0 : {'@': 'String', 'sval': 'b'}
(<RawStmt stmt=<SelectStmt ...
```

--------------------------------

### Verify AST Node Type

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Illustrates how to assert the type of a specific AST node (e.g., `SelectStmt`) using the `pglast.ast` module, ensuring correct node identification.

```python
from pglast import ast
stmt = rawstmt.stmt
assert isinstance(stmt, ast.SelectStmt)
```

--------------------------------

### Programmatically Reformat SQL Statements with pglast.prettify

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Shows how to use the `pglast.prettify` function to reformat SQL statements for improved readability. It demonstrates basic reformatting with default options and advanced formatting using `comma_at_eoln=True` to place commas at the end of lines.

```Python
from pglast import prettify
print(prettify('delete from sometable where value is null'))
DELETE FROM sometable
WHERE value IS NULL
print(prettify('select a,b,c from sometable where value is null'))
SELECT a
     , b
     , c
FROM sometable
WHERE value IS NULL
print(prettify('select a,b,c from sometable'
               ' where value is null or value = 1',
               comma_at_eoln=True))
SELECT a,
       b,
       c
FROM sometable
WHERE value IS NULL
   OR value = 1
```

--------------------------------

### Customize pglast Node Printer for Parameter Replacement

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates how to override the default node printer in pglast to customize the output of SQL statements. This example specifically replaces PostgreSQL parameter references ($1, $2) with actual argument values by defining a custom `node_printer` for `ast.ParamRef` nodes.

```Python
import ast
from pglast.printers import node_printer
from pglast import prettify # Assuming prettify is available from pglast

sql = 'update translations set italian=$2 where word=$1'
print(prettify(sql))
# Expected output:
# UPDATE translations
# SET italian = $2
# WHERE word = $1

# Define arguments for parameter replacement
args = ['Hello', 'Ciao']

@node_printer(ast.ParamRef, override=True)
def replace_param_ref(node, output):
    # Replace parameter reference with the corresponding argument from 'args'
    output.write(repr(args[node.number - 1]))

print(prettify(sql, safety_belt=False))
# Expected output:
# UPDATE translations
# SET italian = 'Ciao'
# WHERE word = 'Hello'
```

--------------------------------

### Compare AST Nodes for Equality

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates how `pglast` nodes can be compared for equality. Nodes are considered equal if all their semantically relevant attributes match, ignoring minor differences like comments in the original SQL.

```python
other_stmt = parse_sql('select /* something here */ 1')[0].stmt
print(other_stmt(depth=2, skip_none=True))
stmt == other_stmt
```

--------------------------------

### Handle SQL Functions with pgpp

Source: https://pglast.readthedocs.io/en/v7/usage

Demonstrates how `pgpp` processes SQL functions, including the default behavior of converting standard SQL functions (like `EXTRACT`) to their `pg_catalog` equivalents, and how to prevent this conversion using the `--special-functions` flag. It also shows how to remove `pg_catalog` prefixes from function calls with `--remove-pg_catalog-from-functions`.

```Shell
$ pgpp -S "select extract(hour from t1.modtime) from t1"
SELECT pg_catalog.date_part('hour', t1.modtime)
FROM t1

$ pgpp --special-functions -S "select extract(hour from t1.modtime) from t1"
SELECT EXTRACT(HOUR FROM t1.modtime)
FROM t1

$ echo "
select substring('123',2,3),
       regexp_split_to_array('x,x,x', ','),
       btrim('xxx'), trim('xxx'),
       POSITION('hour' in trim(substring('xyz hour ',1,6)))
" | pgpp
SELECT pg_catalog.substring('123', 2, 3)
     , regexp_split_to_array('x,x,x', ',')
     , btrim('xxx')
     , pg_catalog.btrim('xxx')
     , pg_catalog.position(pg_catalog.btrim(pg_catalog.substring('xyz hour ', 1, 6))
                         , 'hour')

$ echo "
select substring('123',2,3),
       regexp_split_to_array('x,x,x', ','),
       btrim('xxx'), trim('xxx'),
       POSITION('hour' in trim(substring('xyz hour ',1,6)))
" | pgpp -f --remove-pg_catalog-from-functions
SELECT substring('123', 2, 3)
     , regexp_split_to_array('x,x,x', ',')
     , btrim('xxx')
     , btrim('xxx')
     , pg_catalog.position(btrim(substring('xyz hour ', 1, 6))
                         , 'hour')
```

--------------------------------

### Reconstructing pglast AST from Node Call Result

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Illustrates how a `pglast` AST node can be cloned or reconstructed from the result of calling another node, demonstrating that the new object is a distinct but equivalent copy, not the same instance.

```Python
clone = ast.SelectStmt(stmt())
clone is stmt
False
clone == stmt
True
```

--------------------------------

### Obtain SQL Parse Tree using pgpp

Source: https://pglast.readthedocs.io/en/v7/usage

Illustrates how to use `pgpp` with the `--parse-tree` flag to generate the abstract syntax tree (AST) representation of a given SQL statement. The output is a JSON structure detailing the parsed components of the SQL query.

```Shell
$ pgpp --parse-tree --statement "select 1"
[{'@': 'RawStmt',
  'stmt': {'@': 'SelectStmt',
           'all': False,
           'limitOption': <LimitOption.LIMIT_OPTION_DEFAULT: 0>,
           'op': <SetOperation.SETOP_NONE: 0>,
           'targetList': ({'@': 'ResTarget',
                           'location': 7,
                           'val': {'@': 'A_Const',
                                   'location': 7,
                                   'val': {'@': 'Integer', 'val': 1}}},)},
  'stmt_len': 0,
  'stmt_location': 0}]
```

--------------------------------

### Obtain SQL Parse Tree with pgpp CLI

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Illustrates how to use the `pgpp` command-line tool to obtain the parse tree (Abstract Syntax Tree) of a SQL statement. The output is a JSON representation of the parsed SQL structure, useful for understanding how pglast interprets queries.

```Shell
# Get the parse tree for a simple SELECT statement
pgpp --parse-tree --statement "select 1"
# Expected output (JSON):
# [{
#   '@': 'RawStmt',
#   'stmt': {
#             '@': 'SelectStmt',
#             'all': False,
#             'limitOption': 0,
#             'op': 0,
#             'targetList': ({
#                             '@': 'ResTarget',
#                             'location': 7,
#                             'val': {
#                                     '@': 'A_Const',
#                                     'location': 7,
#                                     'val': {
#                                             '@': 'Integer',
#                                             'val': 1
#                                            }
#                                    }
#                            },)
#           },
#   'stmt_len': 0,
#   'stmt_location': 0
# }]
```

--------------------------------

### pglast Abstract Syntax Tree (AST) Example

Source: https://pglast.readthedocs.io/en/v7/api

This snippet illustrates a portion of the Abstract Syntax Tree (AST) structure generated by pglast after parsing a SQL statement. It shows how function parameters, their types (e.g., 'int4' from 'pg_catalog'), and modes are represented within the AST.

```JSON
                                        'value': 'd'},
                               'name': 'a'},
                              {'@': 'FunctionParameter',
                               'argType': {'@': 'TypeName',
                                           'location': "...",
                                           'names': ({"@": "String",
                                                      "sval": "pg_catalog"},
                                                     {"@": "String",
                                                      "sval": "int4"}),
                                           'pct_type': false,
                                           'setof': false,
                                           'typemod': -1},
                               'mode': {'#': 'FunctionParameterMode',
                                        'name': 'FUNC_PARAM_DEFAULT',
                                        'value': 'd'},
                               'name': 'b'}),
               'replace': false,
               'returnType': {'@': 'TypeName',
                              'location': "...",
                              'names': ({"@": "String", "sval": "pg_catalog"},
                                        {"@": "String", "sval": "int4"}),
                              'pct_type': false,
                              'setof': false,
                              'typemod': -1}},
      'stmt_len': 0,
      'stmt_location': 0}]
```

--------------------------------

### pglast.visitors.Ancestor Basic Usage and Path Traversal

Source: https://pglast.readthedocs.io/en/v7/visitors

This Python example demonstrates how to initialize and use the `pglast.visitors.Ancestor` class to track node ancestry in an AST. It shows how to create paths using the `/` operator, traverse the tree using the `@` operator, and access parent nodes via indexing.

```python
tree = parse_sql('select 1')
root = Ancestor()
root
root@tree is tree
True
root[0] is None
True
select_stmt_path = root / (tree, 0) / (tree[0], 'stmt')
select_stmt_path
select_stmt_path@tree is tree[0].stmt
True
select_stmt_path[0] is tree[0]
True
columns_path = (select_stmt_path
                / (tree[0].stmt, 'targetList'))
first_col_path = (columns_path
                  / (tree[0].stmt.targetList[0], 0))
first_col_path
first_col_path[0]
first_col_path[1] is columns_path[0]
```

--------------------------------

### Pglast SQL Reformatting with `IndentedStream` and `RawStream`

Source: https://pglast.readthedocs.io/en/v7/usage

Explains the underlying mechanism of SQL reformatting in pglast using `IndentedStream` and `RawStream` classes. It demonstrates direct usage of `IndentedStream` for formatting SQL strings with options like `comma_at_eoln`, and shows how `RawStream` can be used to print specific parts of an AST node.

```python
>>> from pglast.stream import IndentedStream, RawStream
>>> print(IndentedStream(comma_at_eoln=True)('select a,b,c from sometable'))
SELECT a,
       b,
       c
FROM sometable

>>> print(IndentedStream()('select foo from bar'))
SELECT foo
FROM bar

>>> sql = 'select a.x, b.y from a join b on a.bid = b.id'
>>> astnode = parse_sql(sql)[0].stmt
>>> astnode
<SelectStmt targetList=(<ResTarget val=<ColumnRef fields=(<String sval='a'>, <String sval='x'>)>>...
>>> print(RawStream()(astnode.fromClause))
a INNER JOIN b ON a.bid = b.id
```

--------------------------------

### Iterate pglast AST node attributes

Source: https://pglast.readthedocs.io/en/v7/usage

This snippet shows how to iterate over a `pglast` AST node (specifically a `RawStmt` instance) to discover its accepted attributes. The output lists the attribute names available for the node.

```Python
rawstmt = root[0]
print(tuple(rawstmt))
('stmt', 'stmt_location', 'stmt_len')
```

--------------------------------

### Normalize SQL Statement with pgpp

Source: https://pglast.readthedocs.io/en/v7/usage

Demonstrates how to use the `pgpp` command-line tool with the `--normalize` (or `-S`) flag to reformat and normalize a SQL statement. This process standardizes casing and removes extraneous whitespace, producing a cleaner SQL output.

```Shell
$ pgpp --normalize -S "select a,b,c from st    where a='foo'"
SELECT a, b, c FROM st WHERE a = 'foo'
```

--------------------------------

### Customize pglast SQL Output with Node Printer

Source: https://pglast.readthedocs.io/en/v7/usage

This snippet shows how to use `pglast.printers.node_printer` to customize the SQL output generated from an AST. By overriding the default printing behavior for `ast.ParamRef` nodes, it allows substituting placeholder values with actual arguments, enabling dynamic SQL generation or parameterization.

```python
sql = 'update translations set italian=$2 where word=$1'
print(prettify(sql))
from pglast.printers import node_printer
@node_printer(ast.ParamRef, override=True)
def replace_param_ref(node, output):
    output.write(repr(args[node.number - 1]))

args = ['Hello', 'Ciao']
print(prettify(sql, safety_belt=False))
```

--------------------------------

### PostgreSQL String and Array Functions with pg_catalog Prefixes

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

This SQL query demonstrates the use of several PostgreSQL built-in functions for string manipulation and array creation. Functions like `substring`, `regexp_split_to_array`, `btrim`, and `position` are explicitly prefixed with `pg_catalog`, indicating their schema. This snippet highlights standard SQL operations within PostgreSQL.

```SQL
SELECT pg_catalog.substring('123', 2, 3)
     , regexp_split_to_array('x,x,x', ',')
     , btrim('xxx')
     , pg_catalog.btrim('xxx')
     , pg_catalog.position(pg_catalog.btrim(pg_catalog.substring('xyz hour ', 1, 6))
                         , 'hour')
```

--------------------------------

### Processing SQL with pgpp to Remove pg_catalog Prefixes

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

This shell command demonstrates how to use `pgpp` (PostgreSQL pretty-printer) to process a SQL query. The input SQL, which includes `pg_catalog` prefixes, is piped to `pgpp` with the `-f` (format) and `--remove-pg_catalog-from-functions` flags. This utility is useful for standardizing SQL code by removing explicit schema prefixes for common functions.

```Shell
echo "
select substring('123',2,3),
       regexp_split_to_array('x,x,x', ','),
       btrim('xxx'), trim('xxx'),
       POSITION('hour' in trim(substring('xyz hour ',1,6)))
" | pgpp -f --remove-pg_catalog-from-functions
```

--------------------------------

### Count AST Node Types with pglast Visitor

Source: https://pglast.readthedocs.io/en/v7/usage

This Python snippet demonstrates how to extend `pglast.visitors.Visitor` to traverse a PostgreSQL Abstract Syntax Tree (AST) and count the occurrences of different node types. It initializes a `collections.Counter` and updates it within the `visit` method for each node encountered, providing a statistical overview of the AST's composition.

```python
from collections import Counter
from pglast.visitors import Visitor

class Stats(Visitor):
    def __call__(self, node):
        self.counters = Counter()
        super().__call__(node)
        return self.counters

    def visit(self, ancestors, node):
        self.counters.update((node.__class__.__name__,))

stats = Stats()
print(stats(parse_sql('select 1')))
```

--------------------------------

### Reformat SQL Statements with pgpp Command-Line Tool

Source: https://pglast.readthedocs.io/en/v7/usage

This section illustrates the usage of the `pgpp` command-line tool for reformatting SQL statements. It demonstrates various formatting options, including default pretty-printing, comma-at-end-of-line style, and handling complex statements like `CASE` expressions and `UPDATE` statements. It also shows how to control line wrapping for long strings in `INSERT` statements.

```bash
echo "select a,b,c from sometable" | pgpp
```

```bash
echo "select a,b,c from sometable" | pgpp --comma-at-eoln
```

```bash
pgpp -S "select a, case when a=1 then 'singular' else 'plural' end from test"
```

```bash
echo 'update "table" set value=123 where value is null' | pgpp
```

```bash
echo "
insert into t (id, description)
values (1, 'this is short enough'),
       (2, 'this is too long, and will be splitted')" | pgpp -s 20
```

--------------------------------

### Parse SQL and get AST root node with pglast

Source: https://pglast.readthedocs.io/en/v7/usage

This Python snippet demonstrates how to parse an SQL statement using `pglast.parse_sql()` to obtain the Abstract Syntax Tree (AST) root node. The output shows the `RawStmt` object representing the parsed SQL.

```Python
from pglast import parse_sql
root = parse_sql('select 1')
print(root)
(<RawStmt stmt=<SelectStmt targetList=(<ResTarget val=<A_Const isnull=False val=<Integer ival=1>>>,) ...,)
```

--------------------------------

### Scanning SQL Statements into Tokens with pglast.parser.scan in Python

Source: https://pglast.readthedocs.io/en/v7/_sources/parser.rst

This example demonstrates how to use the `scan` function from `pglast.parser` to tokenize an SQL statement. It shows how each token is represented as a `namedtuple` containing its start and end indices, name, and kind, allowing for detailed analysis of the SQL structure.

```python
from pglast.parser import scan
stmt = 'select bar as alìbàbà from foo'
tokens = scan(stmt)
print(tokens[0])
print([stmt[t.start:t.end+1] for t in tokens])
```

--------------------------------

### Access and verify pglast AST node type

Source: https://pglast.readthedocs.io/en/v7/usage

This Python example demonstrates accessing a specific attribute (`stmt`) of a `RawStmt` node and verifying its type using `isinstance()`, confirming it's a `SelectStmt` from the `pglast.ast` module.

```Python
from pglast import ast
stmt = rawstmt.stmt
assert isinstance(stmt, ast.SelectStmt)
```

--------------------------------

### pglast.AlternativeSubPlan Class API

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

API documentation for the `AlternativeSubPlan` class in `pglast`, which wraps the PostgreSQL `AlternativeSubPlan` parser node. It describes the constructor and attributes for representing alternative subplans.

```APIDOC
AlternativeSubPlan(subplans=None)
  Wrapper for the homonymous parser node.
  Attributes:
    subplans: tuple
      SubPlan(s) with equivalent results
```

--------------------------------

### APIDOC: CreateForeignServerStmt Class

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

This class wraps the PostgreSQL `CREATE FOREIGN SERVER` parser node. It encapsulates details such as the server name, optional type and version, the associated foreign-data wrapper name, and generic server options.

```APIDOC
class CreateForeignServerStmt(servername=None, servertype=None, version=None, fdwname=None, if_not_exists=None, options=None):
  # Wrapper for the PostgreSQL CREATE FOREIGN SERVER parser node.
  # Corresponds to libpg_query/src/postgres/include/nodes/parsenodes.h#L2870

  servername: str
    # The name of the foreign server.
  servertype: str
    # Optional type of the server.
  version: str
    # Optional version of the server.
  fdwname: str
    # The name of the Foreign Data Wrapper (FDW) to use.
  if_not_exists: bool
    # If True, do nothing if the server already exists.
  options: tuple
    # Generic options for the server.
```

--------------------------------

### Set pglast AST Enum attributes

Source: https://pglast.readthedocs.io/en/v7/usage

These examples illustrate how to set Enum-type attributes on `pglast` AST nodes. It shows assigning values using a plain string (which is looked up in the related Enum class) or a dictionary representation, and the resulting serialized output.

```Python
stmt.limitOption = 'LIMIT_OPTION_COUNT'
pprint(stmt(depth=1, skip_none=True))
{'@': 'SelectStmt',
 'all': True,
 'groupDistinct': False,
 'limitOption': {'#': 'LimitOption', 'name': 'LIMIT_OPTION_COUNT', 'value': 1},
 'op': {'#': 'SetOperation', 'name': 'SETOP_NONE', 'value': 0},
 'targetList': (…,)}
```

```Python
stmt.limitOption = {'#': 'LimitOption', 'name': 'LIMIT_OPTION_WITH_TIES'}
pprint(stmt(depth=1, skip_none=True))
{'@': 'SelectStmt',
 'all': True,
 'groupDistinct': False,
 'limitOption': {'#': 'LimitOption',
                 'name': 'LIMIT_OPTION_WITH_TIES',
                 'value': 2},
 'op': {'#': 'SetOperation', 'name': 'SETOP_NONE', 'value': 0},
 'targetList': (…,)}
```

--------------------------------

### APIDOC: CreateUserMappingStmt Class

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

Wrapper for the PostgreSQL parser node representing a CREATE USER MAPPING statement. It defines attributes for the user, server name, existence check, and generic options.

```APIDOC
CreateUserMappingStmt(user=None, servername=None, if_not_exists=None, options=None)
  user: RoleSpec*
    User role
  servername: str
    Server name
  if_not_exists: bool
    Just do nothing if it already exists?
  options: tuple
    Generic options to server
```

--------------------------------

### Split SQL Statements with pglast

Source: https://pglast.readthedocs.io/en/v7/usage

This example demonstrates using `pglast.split` to separate multiple SQL statements within a single string. By default, it uses the parser, which will raise an error if a statement contains syntax errors. An alternative `with_parser=False` option is shown, which uses a lexical scanner for more robust splitting in the presence of errors.

```python
from pglast import split
for statement in split('select 1; select 2'):
    print(statement)
```

```python
split('select 1 from; select 2')
```

```python
for statement in split('select 1 from; select 2', with_parser=False):
    print(statement)
```

--------------------------------

### ExplainStmt Class API

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

API documentation for the ExplainStmt class, wrapping the PostgreSQL 'EXPLAIN' statement parser node. It includes parameters for the query to explain and explanation options.

```APIDOC
ExplainStmt(query=None, options=None)
  Wrapper for the homonymous parser node.

  Attributes:
    query: Node
      The query (see comments above)
    options: tuple
      List of DefElem nodes
```

--------------------------------

### Iterate and Inspect AST Node Attributes

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Shows how to access and iterate over the attributes of a `RawStmt` node, revealing its internal structure and available properties.

```python
rawstmt = root[0]
print(tuple(rawstmt))
```

--------------------------------

### Handle Invalid Enum Attribute Assignment

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Demonstrates that assigning an incorrect value or an incompatible dictionary to an Enum attribute of an AST node will raise a `ValueError`.

```python
stmt.limitOption = 'foo'
```

```python
stmt.limitOption = {'#': 'JoinType', 'name': 'JOIN_INNER'}
```

--------------------------------

### Class: PartitionElem

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

API documentation for the PartitionElem class, which wraps the PostgreSQL 'PartitionElem' parser node. It defines properties for elements within a partition specification.

```APIDOC
class PartitionElem(name=None, expr=None, collation=None, opclass=None, location=None)
  Wrapper for the homonymous parser node.

  Attribute: name
    Type: str
    Description: Name of column to partition on, or NULL

  Attribute: expr
    Type: Node
    Description: Expression to partition on, or NULL

  Attribute: collation
    Type: tuple
    Description: Name of collation; NIL = default

  Attribute: opclass
    Type: tuple
    Description: Name of desired opclass; NIL = default

  Attribute: location
    Type: ParseLoc
    Description: Token location, or -1 if unknown
```

--------------------------------

### Alter Boolean Attribute of an AST Node

Source: https://pglast.readthedocs.io/en/v7/_sources/usage.rst

Shows how to modify a boolean attribute of an AST node (e.g., `stmt.all`). The library performs checks on assigned values to ensure type correctness.

```python
print(stmt.all)
stmt.all = True
print(stmt.all)
```

```python
stmt.all = "foo"
```

--------------------------------

### pglast.parser Module API Reference

Source: https://pglast.readthedocs.io/en/v7/_sources/parser.rst

Comprehensive API documentation for the `pglast.parser` module, detailing its constants, exceptions, classes, and functions for interacting with `libpg_query`. This includes methods for parsing SQL and PL/pgSQL into various formats, deparsing, fingerprinting, and tokenizing queries.

```APIDOC
Module: pglast.parser
Synopsis: The interface with libpg_query

Constants:
  LONG_MAX: The highest integer that can be stored in a C `long` variable, used as a marker (e.g., in PG's `FetchStmt.howMany` for `FETCH_ALL`).

Exceptions:
  ParseError: Exception representing the error state returned by the parser.
  DeparseError: Exception representing the error state returned by the deparser.

Classes:
  Displacements(string):
    Helper class used to find the index of a Unicode character from its offset in the corresponding UTF-8 encoded array.
    Parameters:
      string: The Unicode string.

Functions:
  deparse_protobuf(buffer: bytes) -> str:
    Returns the SQL statement from the given Protobuf buffer, typically generated by `parse_sql_protobuf()`.
    Parameters:
      buffer: A Protobuf buffer containing a parsed SQL statement.

  fingerprint(query: str) -> str:
    Fingerprints the given SQL query string and returns a hash digest that identifies similar queries. Queries differing only by object names or formatting will yield the same digest.
    Parameters:
      query: The SQL statement(s) as a string.

  get_postgresql_version() -> tuple:
    Returns the PostgreSQL version as a tuple (major, minor, patch).

  parse_sql(query: str) -> tuple:
    Parses the given SQL query string and returns the corresponding parse tree as a tuple of `pglast.ast.RawStmt` instances.
    Parameters:
      query: The SQL statement(s) as a string.

  parse_sql_json(query: str) -> str:
    Parses the given SQL query string and returns `libpg_query`'s JSON-serialized parse tree.
    Parameters:
      query: The SQL statement(s) as a string.

  parse_sql_protobuf(query: str) -> bytes:
    Parses the given SQL query string and returns `libpg_query`'s Protobuf-serialized parse tree.
    Parameters:
      query: The SQL statement(s) as a string.

  parse_plpgsql_json(query: str) -> str:
    Parses the given PL/pgSQL statement string and returns `libpg_query`'s JSON-serialized parse tree.
    Parameters:
      query: The PL/pgSQL statement(s) as a string.

  scan(query: str) -> sequence of tuples:
    Splits the given SQL query into its tokens. Each token is a namedtuple with 'start' (int), 'end' (int), 'name' (str), and 'kind' (str) slots.
    Parameters:
      query: The SQL statement as a string.
```

--------------------------------

### APIDOC: CreatedbStmt Class

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

Wrapper for the PostgreSQL parser node representing a CREATE DATABASE statement. It includes attributes for the database name and a list of definition elements.

```APIDOC
CreatedbStmt(dbname=None, options=None)
  dbname: str
    Name of database to create
  options: tuple
    List of DefElem nodes
```

--------------------------------

### APIDOC: CreateForeignTableStmt Class

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

This class wraps the PostgreSQL `CREATE FOREIGN TABLE` parser node. It includes attributes for the base table definition, the foreign server name, and any additional options specific to the foreign table.

```APIDOC
class CreateForeignTableStmt(base=None, servername=None, options=None):
  # Wrapper for the PostgreSQL CREATE FOREIGN TABLE parser node.
  # Corresponds to libpg_query/src/postgres/include/nodes/parsenodes.h#L2895

  base: CreateStmt
    # The base table definition (e.g., columns).
  servername: str
    # The name of the foreign server.
  options: tuple
    # Generic options for the foreign table.
```

--------------------------------

### pglast.parser Module Core Functions

Source: https://pglast.readthedocs.io/en/v7/introduction

Documentation for the primary parsing and utility functions within the `pglast.parser` module. These functions enable interaction with PostgreSQL's parser, providing capabilities like converting SQL/PL/pgSQL to parse trees in JSON or Protobuf formats, generating unique fingerprints for SQL statements, tokenizing, splitting, and deparsing.

```APIDOC
pglast.parser.parse_sql_json(sql_statement: str) -> dict
  - Description: Parses an SQL statement and returns its parse tree as a JSON-encoded value.
  - Parameters:
    - sql_statement (str): The SQL statement to parse.
  - Returns: A dictionary representing the parse tree, suitable for JSON encoding.

pglast.parser.parse_sql_protobuf(sql_statement: str) -> bytes
  - Description: Parses an SQL statement and returns its parse tree as a Protobuf-encoded value.
  - Parameters:
    - sql_statement (str): The SQL statement to parse.
  - Returns: Bytes representing the Protobuf-encoded parse tree.

pglast.parser.parse_plpgsql_json(plpgsql_statement: str) -> dict
  - Description: Parses a PL/pgSQL statement and returns its parse tree as a JSON-encoded value.
  - Parameters:
    - plpgsql_statement (str): The PL/pgSQL statement to parse.
  - Returns: A dictionary representing the PL/pgSQL parse tree, suitable for JSON encoding.

pglast.parser.fingerprint(sql_statement: str) -> str
  - Description: Returns a hash (fingerprint) of the given SQL statement, which can be used to compare different SQL statements for equivalence.
  - Parameters:
    - sql_statement (str): The SQL statement to generate a fingerprint for.
  - Returns: A string representing the hash of the statement.

pglast.parser.scan(sql_statement: str) -> list
  - Description: Returns a sequence of tokens that compose the given SQL statement.
  - Parameters:
    - sql_statement (str): The SQL statement to tokenize.
  - Returns: A list of tokens extracted from the SQL statement.

pglast.parser.split(sql_statement: str) -> list
  - Description: Returns a sequence of individual statements when the input SQL string contains multiple statements.
  - Parameters:
    - sql_statement (str): The SQL string potentially containing multiple statements.
  - Returns: A list of strings, where each string is a single SQL statement.

pglast.parser.deparse_protobuf(protobuf_data: bytes) -> str
  - Description: Accepts a Protobuf-serialized statement (parse tree) and reconstructs the original SQL statement from it.
  - Parameters:
    - protobuf_data (bytes): The Protobuf-encoded parse tree.
  - Returns: The original SQL statement as a string.

pglast.parser.parse_sql(sql_statement: str) -> object
  - Description: Similar to `parse_sql_json()`, but instead of JSON, it returns the syntax tree represented by a hierarchy of instances of classes implemented in the `pglast.ast` module.
  - Parameters:
    - sql_statement (str): The SQL statement to parse.
  - Returns: An instance of a `pglast.ast` class representing the root of the syntax tree.
```

--------------------------------

### pglast RawStmt Class API

Source: https://pglast.readthedocs.io/en/v7/_sources/ast.rst

Documents the `RawStmt` class, a wrapper for a raw statement parser node in PostgreSQL. It describes the constructor and attributes for the statement's expression tree, its starting location, and its length in bytes.

```APIDOC
class RawStmt(stmt=None, stmt_location=None, stmt_len=None)
  Wrapper for the homonymous parser node.

  Attributes:
    stmt: Node
      Raw parse tree
    stmt_location: ParseLoc
      Start location, or -1 if unknown
    stmt_len: ParseLoc
      Length in bytes; 0 means "rest of string"
```

--------------------------------

### Alter pglast AST node attributes

Source: https://pglast.readthedocs.io/en/v7/usage

These Python snippets demonstrate how to alter attributes of a `pglast` AST node. It shows setting a boolean attribute and the validation performed by the library, raising a `ValueError` if an invalid type is assigned.

```Python
print(stmt.all)
False
stmt.all = True
print(stmt.all)
True
```

```Python
stmt.all = "foo"
Traceback (most recent call last):
  ...
ValueError: Bad value for attribute SelectStmt.all, expected (<class 'bool'>, <class 'int'>), got <class 'str'>: 'foo'
```
Tokens:
Raw