Very simple query decomposition

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:

1. Create view from your query – PostgreSQL will parse query and find all columns
2. Decompose source code of this view
3. If you need to find dependencies than simple check all dependencies of this view in system catalogs