When you have views that were created using complex definitions, there might be instances later on, that you might forget to recall which underlying tables and views they referenced.
You can use the following query to identify the table and view dependencies of your views (or even tables):
SELECT DISTINCT c_p.oid AS tbloid,
n_p.nspname AS dependee_schema,
c_p.relname AS dependee,
n_c.nspname AS dependent_schema,
c_c.relname AS dependent,
c_c.oid AS viewoid
FROM pg_class c_p
JOIN pg_depend d_p ON c_p.relfilenode = d_p.refobjid
JOIN pg_depend d_c ON d_p.objid = d_c.objid
JOIN pg_class c_c ON d_c.refobjid = c_c.relfilenode
LEFT JOIN pg_namespace n_p ON c_p.relnamespace = n_p.oid
LEFT JOIN pg_namespace n_c ON c_c.relnamespace = n_c.oid
WHERE d_p.deptype = 'i'
AND dependee_schema + dependee <> dependent_schema + dependent
AND dependee = 'view_name_or_table_name'
AND dependee_schema = 'schema_name';
On the query above, replace view_name_or_table_name
and schema_name
with the name of your view or table and the schema it belongs to, respectively.
You can use the same query multiple times to understand the full dependency tree from a specific view down to all the actual underlying tables.
To give you an idea of what the query result looks like, here's one from one of our example views named v_heroes_data
which exists under the public
schema:
From the perspective of our v_heroes_data
view, you can see that there are three tables that it depends on.
Comments
0 comments
Please sign in to leave a comment.