with recursive getall as (
select distinct ac.*, ac.table_name||'; ' as hierarchy, 1 as _level
from allconst ac where dep_table_name is null
--and table_schema='...your_schema_in_db...' --optional if you need to narrow select
--and table_name='...your_table_name...'
and dep_table_name is null
union
select c.*, g.hierarchy||c.table_name||'; ' as hierarchy, g._level+1 as _level
from allconst c join getall g on c.dep_table_schema=g.table_schema and c.dep_table_name=g.table_name
--where c.table_schema='...your_schema_in_db...'
),
allconst as (
select ns.nspname as table_schema, c.relname as table_name, c.relkind as rel_kind, con.conname as constraint_name, con.contype, refns.nspname as dep_table_schema, refc.relname as dep_table_name, refc.relkind as dep_rel_kind
from pg_class c
join pg_namespace ns on c.relnamespace=ns.oid
left join pg_constraint con on c.oid=con.conrelid
left join pg_class refc on con.confrelid=refc.oid
left join pg_namespace refns on refc.relnamespace=refns.oid
where c.relkind='r' --only ordinary tables
--and ns.nspname='...schema...'
)
select distinct * from getall
--where hierarchy like '...table_name...;%'
order by hierarchy