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

Post date: Oct 16, 2012 4:59:55 PM

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;