I am interested in listing all the pairwise foreign key dependencies
between tables by name, i.e., if I have table A with some primary key
column A.id, and table B has a foreign key referencing A.id, then table
B depends on A.
I found some things by Google searching, but most of these were not
straightforward SQL. I found one approach that utilized a Ruby script,
for instance, and that approach seemed too complicated.
I envision defining a view that will produce the results ... so I am
aiming for pure SQL. I played around with how I might do this a little
bit (see below), but figured maybe someone already has it, or there is
some information_schema or pg_catalog entity that I did not notice. (I
did see a some tables/views in pg_catalog and information_schema that
look like they might provide the basic information but I was not sure
that exactly what I want is there ... if so, please point it out!)
My preliminary attempt was to capture the output of the following
command to get the SQL statements used to list dependencies for a
particular table "public.city" in a data base named "home":
psql -E -c'\d+ public.city' home
Messing around with that output, I came up with the following query to
list all the dependencies defined by foreign keys:
SELECT
n1.nspname AS primary_key_ns,
c1.relname AS primary_key_table,
n2.nspname AS foreign_key_ns,
c2.relname AS foreign_key_table
FROM pg_catalog.pg_constraint c
JOIN ONLY pg_catalog.pg_class c1 ON c1.oid = c.confrelid
JOIN ONLY pg_catalog.pg_class c2 ON c2.oid = c.conrelid
JOIN ONLY pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
JOIN ONLY pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
WHERE c1.relkind = 'r' AND c.contype = 'f'
ORDER BY 1,2,3,4;
I was hoping mailing list participants could review this to make sure it
is giving me what I expect (as described initially) ... and suggest
improvements.