On 31/07/11 21:42, Alban Hertroys wrote:
> 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
[...]
Thanks, I missed that...