Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4;
create table t1(a int); create table t2(a int); create table t3(a int); create table t4(a int);
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('with c as (select * from t1)select (select count(*) from t2) from c join t3 on true,t4 where exists (select * from t4);');


get_query_tables


{t1,t2,t4,t3,t4}