PostgreSQL如何查询schema中与指定表关联的所有视图

修改表字段类型时触发cannot alter the type of a column used by a view报错，是因为待修改字段被视图依赖，可通过系统表查询所有关联的视图。

查询所有依赖目标表的视图 #

SELECT 
    n.nspname AS view_schema,
    c.relname AS view_name,
    pg_get_viewdef(c.oid) AS view_definition
FROM 
    pg_depend d
JOIN 
    pg_class c ON d.objid = c.oid
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
WHERE 
    d.refobjid = '替换为你的schema名.表名'::regclass
    AND c.relkind = 'v'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY 
    n.nspname, c.relname, c.oid
ORDER BY 
    view_schema, view_name;

该语句会返回所有直接依赖目标表的用户视图，包含视图所属schema、视图名称和完整定义。

查询仅依赖待修改字段的视图 #

如果需要精准定位用到了目标字段的视图，可使用以下查询：

SELECT 
    n.nspname AS view_schema,
    c.relname AS view_name,
    pg_get_viewdef(c.oid) AS view_definition
FROM 
    pg_attribute a
JOIN 
    pg_depend d ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
JOIN 
    pg_class c ON d.objid = c.oid
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
WHERE 
    a.attrelid = '替换为你的schema名.表名'::regclass
    AND a.attname = '替换为待修改的字段名'
    AND c.relkind = 'v'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY 
    n.nspname, c.relname, c.oid;

查询到关联视图后，按以下步骤操作即可正常修改字段类型：

• 备份所有关联视图的定义
• 临时删除所有关联视图
• 执行字段修改语句：alter table schema.tbl_name alter column column_name type varchar(5) using column_name::varchar(5);
• 重新创建之前备份的所有视图

内容的提问来源于stack exchange，提问作者Sridas D