Re: Finding referecing and referenced tables, adaptation from David Fetter's solution

On 30 Jul 2011, at 12:17, Gavin Flower wrote:

>> CREATE OR REPLACE VIEW table_dependencies AS (
>> WITH RECURSIVE t AS (
>> SELECT
>> c.oid AS origin_id,
>> c.oid::regclass::text AS origin_table,
>> c.oid AS referencing_id,
>> c.oid::regclass::text AS referencing_table,
>> c2.oid AS referenced_id,
>> c2.oid::regclass::text AS referenced_table,
>> ARRAY[c.oid::regclass,c2.oid::regclass] AS chain
>> FROM pg_catalog.pg_constraint AS co
>> INNER JOIN pg_catalog.pg_class AS c
>> ON c.oid = co.conrelid
>> INNER JOIN pg_catalog.pg_class AS c2
>> ON c2.oid = co.confrelid
>> -- Add this line as "parameter" if you want to make a one-off query
>> -- or a function instead of a view
>> -- WHERE c.oid::regclass::text = '<table name>'
>>
>> UNION ALL
>> SELECT
>> t.origin_id,
>> t.origin_table,
>> t.referenced_id AS referencing_id,
>> t.referenced_table AS referencing_table,
>> c3.oid AS referenced_id,
>> c3.oid::regclass::text AS referenced_table,
>> t.chain || c3.oid::regclass AS chain
>> FROM pg_catalog.pg_constraint AS co
>> INNER JOIN pg_catalog.pg_class AS c3
>> ON c3.oid = co.confrelid
>> INNER JOIN t
>> ON t.referenced_id = co.conrelid

> I just realized that the 3rd & 4th line will always show the same values as the 1st & 2nd lines, as only the column headings change! Is this intentional?
>
> c.oid AS origin_id,
> c.oid::regclass::text AS origin_table,
> c.oid AS referencing_id,
> c.oid::regclass::text AS referencing_table,

Only the 'root'-nodes of the recursive tree are going through that part of the UNION. Those don't have an origin. It's a matter of choice what to do in that case. Common choices are to make root nodes reference themselves or to set their origins to NULL.
Either case has cons and pros that usually depend on how the query results are used.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4e3523b412093530528260!