How to find dependent views created on any table in Greenplum database

posted Apr 28, 2017, 4:38 PM by Sachchida Ojha
The following query will give you the dependent views for that table:

# select a.*,b.definition from information_schema.view_table_usage
a, pg_views b where a.table_name='test' and
a.view_name=b.viewname;

When you alter an existing table (for example dropping a column), you may want to determine if there are any views that depend on the columns you are changing.

Example:


\dt test
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+------------+---------
public | test | table | gpadmin | heap
create view test_view as (select * from test);

##Run the following query with "where a.table_name='<table_name>'
select a.*,b.definition from information_schema.view_table_usage
a, pg_views b where a.table_name='test' and
a.view_name=b.viewname;
view_catalog | view_schema | view_name | table_catalog |
table_schema | table_name | definition
-------------+------------+-----------+-----------+--------------
+------------+--------------------
ddata | public | test_view | ddata | public
| test | SELECT test.a FROM test;
Comments