[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Finding referecing and referenced tables, adaptation
From:       Gavin Flower <GavinFlower () archidevsys ! co ! nz>
Date:       2011-07-31 10:53:21
Message-ID: 4E353421.5000903 () archidevsys ! co ! nz
[Download RAW message or body]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]