Geek Answers Handbook

Getting a list of tables that a view / table depends on in PostgreSQL

In PostgreSQL, is there a way to get all the tables that the view / table depends on based on the use of foreign keys and access to this table?

Basically, I want to be able to copy the view / table structure using a script and want to be able to automatically get a list of tables that I also need to copy in order to still work to the right.

This answer seems to be heading in the right direction, but does not give me the results that I expect / need. Any suggestions?

+4
source share
4 answers

Using information from Andy Lester, I was able to come up with the following queries to get the information I needed.

Get tables that include foreign keys:

SELECT cl2.relname AS ref_table FROM pg_constraint as co JOIN pg_class AS cl1 ON co.conrelid=cl1.oid JOIN pg_class AS cl2 ON co.confrelid=cl2.oid WHERE co.contype='f' AND cl1.relname='TABLENAME' ORDER BY cl2.relname;

Get tables that view or rules from the table relate to:

 SELECT cl_d.relname AS ref_table FROM pg_rewrite AS r JOIN pg_class AS cl_r ON r.ev_class=cl_r.oid JOIN pg_depend AS d ON r.oid=d.objid JOIN pg_class AS cl_d ON d.refobjid=cl_d.oid WHERE cl_d.relkind IN ('r','v') AND cl_r.relname='TABLENAME' GROUP BY cl_d.relname ORDER BY cl_d.relname;
+8
source

Assuming your foreign keys are configured correctly, use pg_dump to reset table definitions.

 pg_dump -s -t TABLENAME
+2
source

I think this is a pretty bad idea. Just copy the entire database, I think the application wants to have all the data, not just the data from one table. What else, there are also triggers that may depend on some tables, but to know what you have to do is not so easy to analyze the code.

+1
source

In psql, adding + to the regular \d gives you a linked list along with the table definition.

 \d+ tablename
0
source

More articles:


All Articles