Get a list of all used tables in a Postgresql SELECT query

Question

Is there a way to get all the tables used in complex SELECT query in Postgesql without using an actual SQL parser? ver. 9.5 and above will be used.

Answer 1

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