3

I've found an interesting library libpg_query, which allows to parse any arbitrary Postgres SQL query into an internal parse tree, the way Postgres would actually understand the query. It acts offline, it's a rewrite of Postgres' source code. For some, it may be a positive thing, but for me it's not. You wouldn't actually know, which columns would be fetched in a starred expression (SELECT * FROM user), their types, etc.

And it doesn't compile for windows, haha.

But the Postgres client does compile, and this code is in there, but how could it be reached? Does Postgres have any command, which allows to parse queries and return the resulting tree? Kind of like EXPLAIN, but one parsing step earlier. I couldn't find one in the docs, and everyone else just redirects to the forementioned library.

winwin
  • 958
  • 7
  • 25
  • 1
    **SQL parsing** exists, probably for C too. And then you can resolve `*` and index usage yourself. If you compare results of test DB + queries with `explain`, you'll get there. This is more something for a real (w.r.t. errorproneness) programming language, but you might use parser generators. It is bulk abstract work, chapeau! – Joop Eggen Nov 21 '21 at 12:41
  • 1
    This is not done by the PostgreSQL client, but by the server. You can probably write a C function that makes use of the code in `src/backend/parser`. But that wouldn't be stand-alone. – Laurenz Albe Nov 22 '21 at 03:28

2 Answers2

3

You could create a temporary view from the query, then get the query tree from the pg_rewrite system table:

CREATE TEMPORARY VIEW _temp AS [your query];
SELECT ev_action FROM pg_rewrite WHERE rulename='_RETURN' AND ev_class='_temp'::regclass;

That returns a transformed pg_node_tree.

jedediah
  • 1,179
  • 9
  • 20
0

You can use sqlglot, a pure python SQL parser and transpiler that I wrote.

import sqlglot

sqlglot.parse(sql)
Toby Mao
  • 374
  • 2
  • 6