CODEWITHSUNDEEP
sqlpostgresqlmetaprogrammingsql-parser
winwin
winwin

Reputation: 1787

Get internal parse tree of an SQL query

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.

Upvotes: 4

Views: 2311

Answers (2)

jedediah
jedediah

Reputation: 1219

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.

Upvotes: 4

Toby Mao
Toby Mao

Reputation: 574

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

import sqlglot

sqlglot.parse(sql)

Upvotes: 2

Related Questions