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

Question

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

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

This response appears to be headed in the right direction, but doesn't give me the results that I expect/need. Any suggestions?

What results are you looking for? Greg Smith does show the internal views you need to get all dependencies. Maybe you have to tweak it, but that's all. — Frank Heikens, Nov 19 '10 at 21:48
I want to create a schema in the existing database that acts as an "override" of just a portion of the entire database structure so the majority of the tables can be shared and I was hoping to automate this process instead of having to do it by hand and potentially run into problems. The comment by Andy Lester led me to the pg_constraint table for getting the necessary info for foreign keys, but the pg_views and pg_rules don't appear to have the necessary info for grabbing which tables are depended on in a rule or view. — Dave Johansen, Dec 01 '10 at 17:59

score 18 · Accepted Answer · edited Sep 25 '15 at 00:35

18

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

Get Tables that Foreign Keys refer to:

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 a View or Rules from a Table refer 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;

edited Sep 25 '15 at 00:35

jpmc26

28,463
14
94
146

answered Dec 02 '10 at 16:58

Dave Johansen

889
1
7
23

I'm new to pg. how does this join happen? `JOIN pg_depend as d on r.oid=d.objid` - because there is no `oid` column on `pg_rewrite` - scratching my head – ekkis Apr 28 '16 at 02:00
`oid` is an [Object Identifier](https://www.postgresql.org/docs/9.6/static/datatype-oid.html). – Dave Johansen Jul 21 '17 at 18:59
1

If you are using more than one schema you should JOIN `pg_namespace` also like `JOIN pg_namespace AS ns ON cl_d.relnamespace = ns.oid` to select `ns.nspname AS ref_schema` in addition to the table name. – Planetary Dev Oct 22 '17 at 17:43
@ekkis `oid` is an implicit column that you only get to see when you're explicitly asking for it – John Frazer Feb 06 '18 at 19:57

score 2 · Answer 2 · answered Nov 19 '10 at 21:22

2

Assuming you have your foreign keys set up correctly, use pg_dump to dump the table definitions.

pg_dump -s -t TABLENAME

answered Nov 19 '10 at 21:22

Andy Lester

91,102
13
100
152

I was hoping for something that gave me some sort of easy to parse list, but this basically just dumps the SQL queries that were used to create the table/view and would be difficult to use in an automated script. – Dave Johansen Nov 22 '10 at 16:49
1

Oh, so what you really want is pg_catalog. http://www.postgresql.org/docs/8.4/static/catalogs.html – Andy Lester Nov 23 '10 at 00:27
Yes, pg_constraint joined with pg_class gave me exactly what I wanted for the foreign keys, so is there something similar for views and rules? The views pg_rules and pg_views just give you the reconstructed SQL and don't appear to have a way to query the referenced tables as can be done with pg_constraint. – Dave Johansen Dec 01 '10 at 17:55
So a little searching through the system catalog tables revealed the pg_rewrite table and it looks like it contains the information that I need in the ev_action column but it's in a compiled format. Is there a way to pull out the referenced tables from that information? – Dave Johansen Dec 01 '10 at 18:52

score 1 · Answer 3 · answered Nov 19 '10 at 21:27

1

I think it is a quite bad idea. Just copy the whole database, I think that the application wants to have all data, not only data from one table. What's more, there are also triggers, that could depend on some tables, but to know that you'd have to make not so easy code analysis.

answered Nov 19 '10 at 21:27

Szymon Lipiński

27,098
17
75
77

The issue is that we want to create a schema in the existing database that acts as an "override" of just a portion of the entire database structure so the majority of the tables can be shared and I was hoping to automate this process instead of having to do it by hand and potentially run into problems. – Dave Johansen Nov 22 '10 at 16:34
@Dave: Have you explored using pg_dump and maybe creating tools that work with that. Seeing your new question I think this answer is playing out. Not just with the issue stated, but with all the other issues that are lurking in the corners. It seems like it will be a fragile solution. – nate c Feb 14 '11 at 21:16
Can you explain how I'd go about doing this with pg_dump? For example, I have tables A and B with B containing the latest entry from A for each user that's auto-populated by a trigger, and then table C and view D (which joins A and C) and E (which joins B and C). C is large and has no need to be duplicated in the schema, so I just want A, B, D, and E in the schema. How can I go about populating the schema with those tables using pg_dump? – Dave Johansen Feb 15 '11 at 20:19

score 0 · Answer 4 · answered Nov 19 '10 at 21:27

0

In psql, adding + to the usual \d gives you a "Referenced by" list along with the table definition.

\d+ tablename

answered Nov 19 '10 at 21:27

Alison R.

4,204
28
33

I was hoping for something that gave me some sort of easy to parse list, but this basically just dumps the SQL queries that were used to create the table/view and would be difficult to use in an automated script. – Dave Johansen Nov 22 '10 at 16:49
It would not be very hard to parse the referencing table names out of that with a regex: `^\s*TABLE "([^"]*)"` – Alison R. Nov 22 '10 at 16:54
That is true, but I guess I forgot to mention rules in my original question. Parsing out the tables depended on or affected in a rule of that table seems like it would be far more complex to parse out. – Dave Johansen Dec 01 '10 at 17:46

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

4 Answers4

Linked