how to use query_tree_walker to get all relations used in a query

From: Pierre Forstmann <pierre(dot)forstmann(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: how to use query_tree_walker to get all relations used in a query
Date: 2023-08-19 13:26:06
Message-ID: CAM-sOH8Ogr-EmKrVcFwpx1romhyc8t2ttgfzSaWmgxW7kP+t1Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I am trying to get the list of all relations used in a SELECT query using
the post parse analyze hook.

I can get all relations from top level FROM clause but I cannot get them
for a simple subquery like:

select * from t1 where x1=(select max(x2) from t2);

My current code is:

static bool pgds_tree_walker(Query *node, void *context)
{

/*
* from setrefs.c
* extract_query_dependencies_walker
*/

if (node == NULL)
return false;

if (IsA(node, Query))
{
Query *query = (Query *) node;
ListCell *lc;

/* Collect relation OIDs in this Query's rtable */
foreach(lc, query->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);

if (rte->rtekind == RTE_RELATION ||
(rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)) ||
(rte->rtekind == RTE_NAMEDTUPLESTORE &&
OidIsValid(rte->relid)))
elog(INFO, "pgds_tree_walker: relid=%d", rte->relid);
}

/*
* from rewriteHandler.c
* AcquireRewriteLocks
*/
if (rte->rtekind == RTE_SUBQUERY)
return query_tree_walker(rte->subquery, pgds_tree_walker, (void *) context,
QTW_EXAMINE_RTES_BEFORE);

}

/* And recurse ...*/
query_tree_walker(query, pgds_tree_walker, (void *) context,
QTW_EXAMINE_RTES_BEFORE);
}
}

/*
* build_rel_array
*/
static void pgds_build_rel_array(Query *query)
{
ListCell *cell;
Oid rel_id;
void * context = NULL;
bool result;

foreach(cell, query->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(cell);
rel_id = rte->relid;
if (pgds_rel_index < MAX_REL )
{
pgds_rel_array[pgds_rel_index] = rel_id;
pgds_rel_index++;
}
else elog(ERROR, "pgds_build_rel_array: too many relations (%d)", MAX_REL);
}

result = query_tree_walker(query, pgds_tree_walker, context,
QTW_EXAMINE_RTES_BEFORE);
}

Could someone help me ?

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-08-19 13:35:30 Re: case and accent insensitive search under Python ?
Previous Message Siddharth Karandikar 2023-08-19 12:29:54 Re: Failing to compile with ICU support error on Ubuntu