有没有一个 PostgreSQL 命令可以列出/删除所有物化视图？

Question

我在我的代码中创建了多个视图，每次运行代码时，我想删除到目前为止生成的所有物化视图。 有没有命令可以列出Postgres的所有物化视图或删除它们所有?

- user1150989

Answer 1

纯 SQL

显示所有：

SELECT oid::regclass::text
FROM   pg_class
WHERE  relkind = 'm';

根据在从regclass到text的转换中，您当前在search_path中需要时，名称将自动加上双引号并进行架构限定。

在系统目录pg_class中，材料化视图用relkind = 'm'标记。
手册:

m = materialized view

为了删除全部内容，您可以使用以下查询生成所需的SQL脚本：

SELECT 'DROP MATERIALIZED VIEW ' || string_agg(oid::regclass::text, ', ') 
FROM   pg_class
WHERE  relkind = 'm';

返回：

DROP MATERIALIZED VIEW mv1, some_schema_not_in_search_path.mv2, ...

一条DROP MATERIALIZED VIEW语句可以处理多个物化视图。如果有嵌套视图，您可能需要在末尾添加CASCADE。

在执行之前，请检查生成的DDL脚本以确保没有问题。您确定要从数据库中的所有模式中删除所有 MV 吗？并且您是否具备所需的权限？（当前，在新的标准安装中没有任何物化视图。）

psql中的元命令

在默认的交互式终端psql中，您可以使用元命令：

\dm

在服务器上执行此查询：

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('m','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

这可以简化为：

SELECT n.nspname as "Schema"
     , c.relname as "Name"
     , pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM   pg_catalog.pg_class c
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'm'
AND    n.nspname <> 'pg_catalog'
AND    n.nspname <> 'information_schema'
AND    n.nspname !~ '^pg_toast'
AND    pg_catalog.pg_table_is_visible(c.oid)
ORDER  BY 1,2;

- Erwin Brandstetter

---

上面可能是一个打字错误 - 在我的PG中，上面第三个列表应该以以下方式开始：SELECT 'DROP MATERIALIZED VIEW' ... - Michael Terry

@MichaelTerry：是的，我偏离了话题，谈到了视图。谢谢指出，已经修正。 - Erwin Brandstetter

只是作为一个附注，您可以使用pg快速命令\dm从cli客户端获取视图列表。 - lbrindze

Answer 2

您还可以使用 pg_matviews 系统视图查看所有物化视图。该视图将为您提供信息，包括物化视图的定义，以及物化视图是否已填充或为空（ispopulated 列）。

select * 
from pg_matviews
where matviewname = 'my_materialized_view';

- Mano

Answer 3

这个答案基于Erwin Brandstetter的回答。下面的版本将特定的模式名称添加到只检索来自定义模式的物化视图。级联还会删除该模式中物化视图的依赖关系。请小心使用。

SELECT 'DROP MATERIALIZED VIEW <<schema_name>>.' || c.relname::text || ' CASCADE;' AS drop_statements
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
AND c.relkind = 'm'
AND n.nspname = '<<schema_name>>'

- Stefan

Answer 4

如果您想要获取一个完整列表并在每个视图前面使用DROP语句，那么这会更容易：

SELECT 'DROP MATERIALIZED VIEW ' || relname || ';' 
FROM   pg_class
WHERE  relkind = 'm';

- durbin510