postgresqlfunctionviewalter-tabledatabase-optimization

Is there any way to list all the views related to a table in the existing postgres schema


I got a Postgres database with multiple schemas. I'm trying to optimise my database tables with optimal data types. more often I end with the error

cannot alter the type of a column used by a view

while using the query alter table schema.tbl_name alter column column_name type varchar(5) using column_name::varchar(5);

Is there any way (function) that I could list all the views related to the table?


Solution

  • Use this query:

    select
      u.view_schema schema_name,
      u.view_name,
      u.table_schema referenced_table_schema,
      u.table_name referenced_table_name,
      v.view_definition
    from information_schema.view_table_usage u
    join information_schema.views v on u.view_schema = v.table_schema
      and u.view_name = v.table_name
    where u.table_schema not in ('information_schema', 'pg_catalog')
    order by u.view_schema, u.view_name
    

    Credit: Dataedo.com's article List tables used by a view in PostgreSQL database