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

[Jeremy Finzel <finzelj@xxxxxxxxx>]

On Wed, Dec 13, 2017 at 9:54 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

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?

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