Re: Dependency tree to tie type/function deps to a table

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Wed, Dec 13, 2017 at 10:20 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Jeremy Finzel <finzelj@xxxxxxxxx> 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;