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

On Wed, Dec 13, 2017 at 9:54 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Wed, Dec 13, 2017 at 10:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Jeremy Finzel <finzelj(at)gmail(dot)com> 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?
>>
>
Thanks Tom, I am working on this.

>
>> 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.
>

I appreciate that, Melvin. However, this doesn't do the recursive part.
It doesn't show me type or function dependencies, for example:

CREATE TEMP TABLE foo1 (id int);
CREATE TEMP TABLE foo2 (id int);
CREATE VIEW pg_temp.foo3 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo2 f2;
CREATE VIEW pg_temp.foo4 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo3 f2;

CREATE FUNCTION foo() RETURNS SETOF foo1 AS 'SELECT * FROM foo1;' LANGUAGE
SQL;

Your function only shows:
schema | parent | dep_schema | dependent | type
-----------+-----------+------------+-----------+-------
pg_temp_4 | foo1 | pg_temp_4 | foo3 | view
pg_temp_4 | foo1 | pg_temp_4 | foo4 | view
pg_temp_4 | foo2 | pg_temp_4 | foo3 | view
pg_temp_4 | foo3 | pg_temp_4 | foo4 | view

Thanks,
Jeremy