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

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?
[...]
   SELECT
         c1.oid::regclass::text  AS origin_table,
         c2.oid                  AS referenced_id,
         c2.oid::regclass::text  AS referenced_table,
         ARRAY[c1.oid::regclass,c2.oid::regclass] AS chain
     FROM
         pg_catalog.pg_constraint    AS co,
         pg_catalog.pg_class         AS c1,
         pg_catalog.pg_class         AS c2
     WHERE
         c1.oid = co.conrelid AND
         c2.oid = co.confrelid
[...]

Cheers,
Gavin