Product Documentation

Appearance

All platforms

yb_get_view_table_usage() ​

Return information about views and the tables they depend on.

yb_get_view_table_usage()

This function returns a six-column result set, with the name of the view, its schema, and its database, followed by each table that the view depends on, its schema, and its database.

For example, the view join_all_tables depends on five tables, and the view vteam depends only on the team table:

premdb=# select * from yb_get_view_table_usage() where view_database='premdb';
 view_database | view_schema |    view_name    | table_database | table_schema | table_name 
---------------+-------------+-----------------+----------------+--------------+------------
 premdb        | public      | join_all_tables | premdb         | public       | season
 premdb        | public      | join_all_tables | premdb         | public       | team
 premdb        | public      | join_all_tables | premdb         | public       | hometeam
 premdb        | public      | join_all_tables | premdb         | public       | awayteam
 premdb        | public      | join_all_tables | premdb         | public       | match
 premdb        | public      | vteam           | premdb         | public       | team
(6 rows)

In this example, the function returns a view and a table that have the same name and database but different schemas:

premdb=# create view premdb.bobr.season as select * from premdb.public.season;
CREATE VIEW
premdb=# select * from yb_get_view_table_usage() where view_name=table_name;
 view_database | view_schema | view_name | table_database | table_schema | table_name 
---------------+-------------+-----------+----------------+--------------+------------
 premdb        | bobr        | season    | premdb         | public       | season
(1 row)

Note: You cannot create views and tables that store the results of this function.