Re: Finding referecing and referenced tables, adaptation from David Fetter's solution - Mailing list pgsql-general

From Gavin Flower
Subject Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
Date
Msg-id 4E35318A.10705@archidevsys.co.nz
Whole thread Raw
In response to Re: Finding referecing and referenced tables, adaptation from David Fetter's solution  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
List pgsql-general
On 31/07/11 21:36, Alban Hertroys wrote:
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 know in some situations that they can improve performance - but was this the case here, or is there some other subtlety that I have missed?

Explicit inner joins provide a means to separate the join conditions from other result filtering conditions. Each join is kept 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 get a little more complicated.

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

Alban Hertroys
[...]
Formatting, and habits gained before I fully realized the significance of explicit inner joins.  I appreciate your answer (keeping the joins separate from the filter conditions) - I think I might adopt the same approach, though I hate the cumbersome formatting.

Q. What is the difference between an Experienced Developer and a Trainee?
A. A
n Experienced Developer makes mistakes at a faster rate!
(Or am I being to cynical???)

pgsql-general by date:

Previous
From: Gavin Flower
Date:
Subject: Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
Next
From: Simon Riggs
Date:
Subject: Re: Statistics about Streaming Replication deployments in production