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

A coworker of mine* was looking for a way to quickly and easily be
able to tell which tables were referencing particular table(s) she
wanted to load (for unit testing). Using the examples from David
Fetter**, she submitted a revised version that seems to work quite
well. With her permission, I have posted her changes and comments.

Here is what she had to say about her changes (lightly-edited to mask
table names etc)

"The queries linked in the original post by David Fetter do not appear
to take into account cases where table A references table B, and B
references A.  (Or longer cycles.) In our environment, we have a lot
of those.
Using the original query as a cheat sheet, I wrote a query that
essentially takes as parameter the name of the table whose "ancestors"
you want to find, and comes up with a list of everything it depends
on, but stops as soon as it detects a cycle.

To get a list of all the FK relationships that start at the table of interest:

SELECT referenced_table, depth, chain FROM table_dependencies WHERE
origin_table = '<table name>';

To get a simple list of all tables that a given table requires due to
FK dependencies (which is what I need for my unit tests):

SELECT DISTINCT referenced_table FROM table_dependencies WHERE
origin_table = '<table name>';

To get a partial ordering of the dependencies -- which should tell you
in what order the tables should be populated in order to avoid FK
violations -- I think you can do this:

SELECT referenced_table, MIN(depth) AS path_length
FROM table_dependencies
WHERE origin_table = '<table name>'
GROUP BY referenced_table
ORDER BY path_length DESC;"

-- Here is the view she came up with:

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
    WHERE
        -- prevent infinite recursion by pruning paths where the last entry in
        -- the path already appears somewhere else in the path
        NOT (
            ARRAY[ t.chain[array_upper(t.chain, 1)] ] -- an array
containing the last element
            <@                                        -- "is contained by"
            t.chain[1:array_upper(t.chain, 1) - 1]    -- a slice of the chain,
                                                      -- from element 1 to n-1
        )
)
SELECT  origin_table,
        referenced_table,
        array_upper(chain,1) AS "depth",
        array_to_string(chain,',') as chain
FROM t
);


If anyone has any fixes or changes, or knows of a better way to get
the referencers/referencees, we'd be interested in hearing about them.

* Jenny van Hoof deserves the credit for the changes
** http://people.planetpostgresql.org/dfetter/index.php?/archives/27-Key-Tree.html