Search Postgresql Archives

How to ;ist all table foreign key dependency relationships

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux