Crate pg_query 

pg_query.rs  

This Rust library uses the actual PostgreSQL server source to parse SQL queries and return the internal PostgreSQL parse tree.

It also allows you to normalize queries (replacing constant values with $1, etc.) and parse these normalized queries into a parse tree again.

When you build this library, it builds parts of the PostgreSQL server source (see libpg_query), and then statically links it into this library.

You can find further examples and a longer rationale for the original Ruby implementation here. The Rust version tries to have a very similar API.

Examples

Parsing a query

use pg_query::NodeRef;

let result = pg_query::parse("SELECT * FROM contacts", 0);
assert!(result.is_ok());
let result = result.unwrap();
assert_eq!(result.tables(), vec!["contacts"]);
assert!(matches!(result.protobuf.nodes()[0].0, NodeRef::SelectStmt(_)));

Normalizing a query

let result = pg_query::normalize("SELECT 1 FROM x WHERE y = (SELECT 123 FROM a WHERE z = 'bla')").unwrap();
assert_eq!(result, "SELECT $1 FROM x WHERE y = (SELECT $2 FROM a WHERE z = $3)");

Fingerprinting a query

let result = pg_query::fingerprint("SELECT * FROM contacts.person WHERE id IN (1, 2, 3, 4);", 0, 0).unwrap();
assert_eq!(result.hex, "5735f5c64dd9f68e");

Fingerprinting allows you to identify similar queries that are different only because of the specific object being queried (e.g. different object ids in the WHERE clause) or because of formatting. It is intended to be a superset of the Postgres queryid mechanism: constant values are ignored, so ... WHERE id = 123 and ... WHERE id = 456 get the same fingerprint.

Postgres 18 significantly changed how schema names and table aliases in FROM clauses are handled for queryid, and libpg_query follows that by default:

• Postgres 17 and earlier: the schema name was significant, and table aliases were always ignored.
• Postgres 18 (default): schema names are ignored, and a table alias (if present) is used instead of the relation name.

// Postgres 18 behaviour (default): schema is ignored and the alias replaces the relation name
let a = pg_query::fingerprint("SELECT * FROM public.users u", 0, 0).unwrap();
let b = pg_query::fingerprint("SELECT * FROM myschema.users u", 0, 0).unwrap();
assert_eq!(a.hex, b.hex);

// Postgres 17 compatible fingerprints take the schema into account
let a = pg_query::fingerprint("SELECT * FROM public.users u", 0, pg_query::FingerprintOptions::RANGEVAR_PG17_COMPAT).unwrap();
let b = pg_query::fingerprint("SELECT * FROM myschema.users u", 0, pg_query::FingerprintOptions::RANGEVAR_PG17_COMPAT).unwrap();
assert_ne!(a.hex, b.hex);

Available fingerprint options (combinable, passed as the third fingerprint argument to pg_query::fingerprint):

FingerprintOptions	effect
DEFAULT (0)	Postgres 18 query ID behaviour
RANGEVAR_IGNORE_ALIASES	relation names are always fingerprinted; aliases are ignored
RANGEVAR_INCLUDE_SCHEMA	schema names are also fingerprinted in SELECT/DML statements
RANGEVAR_PG17_COMPAT	combination of the two above, matching Postgres 17 (and libpg_query 17) fingerprints
FULL_RELNAME	fingerprint the full relation name instead of ignoring 2+ consecutive digits (which by default groups date/number-suffixed partitions together)

See https://github.com/pganalyze/libpg_query/wiki/Fingerprinting for the full fingerprinting rules.

Scanning a query’s tokens without protobuf

let tokens = pg_query::scan_tokens("SELECT 1").unwrap();
assert_eq!(tokens.len(), 2);
assert_eq!(tokens[0].start, 0);
assert_eq!(tokens[0].end, 6);

Detecting utility statements

let result = pg_query::is_utility_stmt("SELECT 1; SHOW fsync;").unwrap();
assert_eq!(result, vec![false, true]);

Deparsing a query

use pg_query::DeparseOptions;

let parsed = pg_query::parse("SELECT 1 -- comment\n", 0).unwrap();
let output = pg_query::deparse(&parsed.protobuf, DeparseOptions::default()).unwrap();
assert_eq!(output, "SELECT 1");

// With comments extracted and re-inserted, pretty-printed:
let comments = pg_query::deparse_comments_for_query("SELECT 1 -- comment\n").unwrap();
let output = pg_query::deparse(
    &parsed.protobuf,
    DeparseOptions {
        comments,
        pretty_print: true,
        ..Default::default()
    },
)
.unwrap();
assert!(output.contains("-- comment"));

Truncating a query

let query = "INSERT INTO \"x\" (a, b, c, d, e, f) VALUES ($1)";
let result = pg_query::parse(query, 0).unwrap();
assert_eq!(result.truncate(32).unwrap(), "INSERT INTO x (...) VALUES (...)");

Credits

Thanks to Paul Mason for his work on pg_parse that this crate is based on.

After version 0.6.0, Paul donated the pg_query crate to the pganalyze team. pg_parse is a lighter alternative that focuses on query parsing, while pg_query aims for feature parity with the Ruby gem.

License

PostgreSQL server source code, used under the PostgreSQL license.
Portions Copyright (c) 1996-2023, The PostgreSQL Global Development Group
Portions Copyright (c) 1994, The Regents of the University of California

All other parts are licensed under the MIT license, see LICENSE file for details.
Copyright (c) 2021 Paul Mason paul@form1.co.nz Copyright (c) 2021-2023, Duboce Labs, Inc. (pganalyze) team@pganalyze.com

Re-exports

pub use protobuf::Node;

pub use protobuf::node::Node as NodeEnum;

Modules

protobuf

Structs

DeparseComment

A comment to re-insert into deparsed output, mirroring the C PostgresDeparseComment struct. Comments are produced by deparse_comments_for_query and fed back into deparse via DeparseOptions::comments.

DeparseOptions

Options that control how a parse tree is deparsed, mirroring the C PostgresDeparseOpts struct. Use struct update syntax for a partial configuration:

FilterColumn

Fingerprint

Represents the resulting fingerprint containing both the raw integer form as well as the corresponding 16 character hex value.

FingerprintOptions

Options that control how fingerprints are calculated, mirroring the fingerprint options of the C pg_query_fingerprint_opts API.

Function

ParseResult

Result from calling parse

ParserOptions

Options that control how a query string is parsed, mirroring the parser options of the C pg_query_parse_opts/pg_query_fingerprint_opts API.

SummaryResult

Result from calling summary. Where possible, this is API-compatible with ParseResult.

Table

Enums

Context

Error

Error structure representing the basic error scenarios for pg_query.

LockMode

NodeMut

NodeRef

TriggerType

Functions

deparse

Converts a parsed tree back into a string.

deparse_comments_for_query

Extract the comments from a query, along with the metadata needed to re-insert them into deparsed output via DeparseOptions::comments.

fingerprint

Fingerprints the given SQL statement. Useful for comparing parse trees across different implementations of libpg_query.

is_utility_stmt

Determine whether each statement in a query is a utility statement.

normalize

Normalizes the given SQL statement, returning a parametized version.

parse

Parses the given SQL statement into the given abstract syntax tree.

parse_plpgsql

An experimental API which parses a PLPGSQL function. This currently returns the raw JSON structure.

scan

Scan a sql query into a its component of tokens.

scan_tokens

Scan a sql query into its component tokens, without protobuf serialization.

split_with_parser

Split a well-formed query into separate statements.

split_with_scanner

Split a potentially-malformed query into separate statements. Note that invalid tokens will be skipped

summary

Parses the given SQL statement and provides a summary of it.

truncate

Type Aliases

Result

Convenient Result alias for returning pg_query::Error.