Get list of all used tables in SELECT Postgresql query

Question

Get list of all used tables in SELECT Postgresql query

Is there a way to get all tables used in a complex SELECT query in Postgesql without using an actual SQL parser? faith. 9.5 and up.

+3

postgresql delphi lazarus

xc218m Jun 28. 17 at 19:46

source to share

2 answers

TableList:=TStringList.Create; 
pgConnection1.GetTableNames(TableList,False);

-2

becsystems June 29. 17 at 7:29

source to share

Abelisto · Accepted Answer · 2017-06-28T20:12:23+0000

Try:

create or replace function get_query_tables(p_query text) returns text[] language plpgsql as $$
declare
  x xml;
begin
  execute 'explain (format xml) ' || p_query into x;
  return xpath('//explain:Relation-Name/text()', x, array[array['explain', 'http://www.postgresql.org/2009/explain']])::text[];
end $$;

select get_query_tables('your query here');

dbfiddle

Get list of all used tables in SELECT Postgresql query

More articles: