Re: Dependency tree to tie type/function deps to a table - Mailing list pgsql-general
On Wed, Dec 13, 2017 at 9:54 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
I appreciate that, Melvin. However, this doesn't do the recursive part. It doesn't show me type or function dependencies, for example:
On Wed, Dec 13, 2017 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Jeremy Finzel <finzelj@gmail.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 laneJeremy ,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.
CREATE TEMP TABLE foo1 (id int);
CREATE TEMP TABLE foo2 (id int);
CREATE VIEW pg_temp.foo3 AS
FROM foo1 f CROSS JOIN foo2 f2;
CREATE VIEW pg_temp.foo4 AS
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
pgsql-general by date: