On Wed, Dec 13, 2017 at 10:20 AM, Tom Lane <[email protected]> wrote:
> Jeremy Finzel <[email protected]> writes:
> > It looks like the very useful dependency tree shown when using DROP
> CASCADE
> > is written in C in dependency.c, but there is no way to leverage this
> > within Postgres to actually query an object's dependencies. Can we get
> > this somehow as a Postgres client in SQL?
>
> Seems like you could build a query for that easily enough using a
> recursive union over pg_depend plus pg_describe_object() to produce
> text descriptions of the entries.
>
> regards, tom lane
>
>
Jeremy ,
per Tom
>Seems like you could build a query...
Attached is the query that I use. Hope that helps you.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
SELECT n.nspname as schema,
pa.relname as parent,
nc.nspname as dep_schema,
ch.relname as dependent,
'table' as type
FROM pg_constraint cn
JOIN pg_class pa ON (pa.oid = cn.confrelid)
JOIN pg_class ch ON (ch.oid = cn.conrelid)
JOIN pg_namespace nc ON (nc.oid = cn.connamespace)
JOIN pg_namespace n ON (n.oid = pa.relnamespace)
WHERE pa.relname LIKE '%%'
AND contype = 'f'
UNION
SELECT v.table_schema as schema,
v.table_name as parent,
v.view_schema as dep_schema,
v.view_name as dependent,
'view' as type
FROM information_schema.view_table_usage v
WHERE v.table_name LIKE '%%'
AND v.table_schema <> 'information_schema'
AND v.table_schema <> 'pg_catalog'
ORDER BY 1, 2, 3, 4;
