This is just small simple hint but maybe someone could need it.
If you have some not complicated query you can decompose it to parts using this query:
with srcquery as (select replace(lower( --this is some "normalization of the query string 'SELECT column1, count(*) FROM myschema.mytable where column2 is true group by columnt order by column1' --this is your query you want to analyze ),E'\n',' ') as _query ) select split_part(split_part(_query,'select',2),'from',1) as _columns_part, split_part(split_part(_query,'from',2),'where',1) as _from_part, split_part(split_part(split_part(_query,'where',2),'group by',1),'order by',1) as _where_part, split_part(split_part(_query,'group by',2),'order by',1) as _group_by_part, split_part(_query,'order by',2) as _order_by_part from srcquery
It is not exactly “incredibly clever” but sometimes it is enough.
If you can improve this decomposition you can try this – also in function:
- Create view from your query – PostgreSQL will parse query and find all columns
- Decompose source code of this view
- If you need to find dependencies than simple check all dependencies of this view in system catalogs