Need help with pg_query?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

pganalyze
515 Stars 53 Forks BSD 3-Clause "New" or "Revised" License 484 Commits 7 Opened issues

Description

Ruby extension to parse, deparse and normalize SQL queries using the PostgreSQL query parser

Services available

!
?

Need anything else?

Contributors list

pgquery [ ](https://rubygems.org/gems/pgquery)

This Ruby extension uses the actual PostgreSQL server source to parse SQL queries and return the internal PostgreSQL parsetree.

In addition the extension allows you to normalize queries (replacing constant values with ?) and parse these normalized queries into a parsetree again.

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

This is slightly crazy, but is the only reliable way of parsing all valid PostgreSQL queries.

You can find further examples and a longer rationale here: https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html

Installation

gem install pg_query

Due to compiling parts of PostgreSQL, installation might take a while on slower systems. Expect up to 5 minutes.

Usage

Parsing a query

PgQuery.parse("SELECT 1")

=> #<:parserresult:0x00007fb69a958820 version: stmts: stmt: select_stmt: distinct_clause: target_list: res_target: name: indirection: val: a_const: integer: ival:> >, location: 7 > >, location: 7 > > ], from_clause: [], group_clause: [], window_clause: [], values_lists: [], sort_clause: [], limit_option: :LIMIT_OPTION_DEFAULT, locking_clause: [], op: :SETOP_NONE, all: false > >, stmt_location: 0, stmt_len: 0 > ] >, @warnings=[]> </:parserresult:0x00007fb69a958820>

Modifying a parsed query and turning it into SQL again

parsed_query = PgQuery.parse("SELECT * FROM users")

Modify the parse tree in some way

parsed_query.tree.stmts[0].stmt.select_stmt.from_clause[0].range_var.relname = 'other_users'

Turn it into SQL again

parsed_query.deparse => "SELECT * FROM other_users"

Parsing a normalized query

# Normalizing a query (like pg_stat_statements in Postgres 10+)
PgQuery.normalize("SELECT 1 FROM x WHERE y = 'foo'")

=> "SELECT $1 FROM x WHERE y = $2"

Parsing a normalized query (pre-Postgres 10 style)

PgQuery.parse("SELECT ? FROM x WHERE y = ?")

=> #<:parserresult:0x00007fb69a97a5d8 from x where y='?",' ...>, @warnings=[]> </:parserresult:0x00007fb69a97a5d8>

Extracting tables from a query

PgQuery.parse("SELECT ? FROM x JOIN y USING (id) WHERE z = ?").tables

=> ["x", "y"]

Extracting columns from a query

PgQuery.parse("SELECT ? FROM x WHERE x.y = ? AND z = ?").filter_columns

=> [["x", "y"], [nil, "z"]]

Fingerprinting a query

PgQuery.parse("SELECT 1").fingerprint

=> "50fde20626009aba"

PgQuery.parse("SELECT 2; --- comment").fingerprint

=> "50fde20626009aba"

Faster fingerprint method that is implemented inside the native C library

PgQuery.fingerprint("SELECT ?")

=> "50fde20626009aba"

Differences from Upstream PostgreSQL

This gem is based on libpg_query, which uses the latest stable PostgreSQL version, but with a patch applied to support parsing normalized queries containing

?
replacement characters.

Supported Ruby Versions

Currently tested and officially supported Ruby versions:

  • CRuby 2.5
  • CRuby 2.6
  • CRuby 2.7
  • CRuby 3.0

Not supported:

  • JRuby:
    pg_query
    relies on a C extension, which is discouraged / not properly supported for JRuby
  • TruffleRuby: GraalVM does not support sigjmp, which is used by the Postgres error handling code (
    pg_query
    uses a copy of the Postgres parser & error handling code)

Developer tasks

Update libpg_query source

In order to update to a newer Postgres parser, first update libpg_query to the new Postgres version and tag a release.

Once that is done, follow the following steps:

  1. Update

    LIB_PG_QUERY_TAG
    and
    LIB_PG_QUERY_SHA256SUM
    in
    Rakefile
  2. Run

    rake update_source
    to update the source code
  3. Commit the

    Rakefile
    and the modified files in
    ext/pg_query
    to this source tree and make a PR

Resources

See libpg_query for pgquery in other languages, as well as products/tools built on pgquery.

Original Author

Special Thanks to

License

Copyright (c) 2015, pganalyze Team [email protected]
pg_query is licensed under the 3-clause BSD license, see LICENSE file for details.

Query normalization code:
Copyright (c) 2008-2015, PostgreSQL Global Development Group

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.