postgresql 有没有什么方法可以列出现有postgres模式中与一个表相关的所有视图

nc1teljy  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(103)

我得到了一个Postgres数据库与多个模式。我试图优化我的数据库表与最佳的数据类型。更多的时候,我结束与错误
无法更改视图使用的列的类型
在使用查询alter table schema.tbl_name alter column column_name type varchar(5) using column_name::varchar(5);
有没有什么方法(功能)可以列出与表相关的所有视图?

3hvapo4f

3hvapo4f1#

使用此查询:

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

字符串
图片来源:Dataedo.com的文章List tables used by a view in PostgreSQL database

bvhaajcl

bvhaajcl2#

基于Bohemian's answer,下面是一个视图和后续查询,它将为某个表生成所有视图。
但是,它不会捕获依赖于依赖于表的视图的视图。

CREATE OR REPLACE VIEW dba.v_views_on_table AS
select vtu.view_schema
     , vtu.view_name 
     , vtu.table_schema
     , vtu.table_name
     , v.view_definition
from information_schema.view_table_usage vtu
join information_schema.views v on (vtu.view_schema = v.table_schema
  and vtu.view_name = v.table_name)
where vtu.table_schema not in ('information_schema', 'pg_catalog')
order by vtu.view_schema, vtu.view_name
;

\pset format unaligned
\pset tuples_only 
\o sometable_views.sql
select format('CREATE VIEW %s AS %s %s'
             , u.view_schema||'.'||u.view_name
             , E'\n'
             , 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||'.'||u.table_name = 'someschema.sometable'
order by u.view_schema, u.view_name
;

字符串

相关问题