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

On 30 Jul 2011, at 13:49, Gavin Flower wrote:

> On 30/07/11 10:45, bricklen 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
>> [...]
> I am curious about the explicit use of INNER JOINs, I find them cumbersome, so I rewrote the code to remove them, I
knowin some situations that they can improve performance - but was this the case here, or is there some other subtlety
thatI have missed? 

Explicit inner joins provide a means to separate the join conditions from other result filtering conditions. Each join
iskept with its relevant conditions even, so it's immediately clear which conditions pertain to which joins. 

I find in general explicit inner joins improve readability of queries over implicit joins, especially when the joins
geta little more complicated. 

Perhaps what you find cumbersome about them is just a matter of formatting?

Alban Hertroys

--
The scale of a problem often equals the size of an ego.



!DSPAM:737,4e35223312092713185518!