postgresql 是否有 postgres 命令列出/删除所有物化视图？

Is there a postgres command to list/drop all materialized views?

提问by user1150989

I am creating multiple views in my code and each time the code is run, I would like to drop all the materialized views generated thus far. Is there any command that will list all the materialized views for Postgres or drop all of them?

我在我的代码中创建了多个视图，每次运行代码时，我想删除迄今为止生成的所有物化视图。是否有任何命令可以列出 Postgres 的所有物化视图或删除所有这些视图？

回答by Erwin Brandstetter

Show all:

显示所有：

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

Names are automatically double-quoted and schema-qualified where needed according to your current search_pathin the cast from regclassto text.

根据您当前search_path在从regclassto的演员表中的需要，名称会自动双引号和模式限定text。

In the system catalog pg_classmaterialized views are tagged with relkind = 'm'.
The manual:

在系统目录中，实体pg_class化视图被标记为relkind = 'm'。
手册：

m = materialized view

m = materialized view

To dropall, you can generate the needed SQL script with this query:

要删除所有内容，您可以使用以下查询生成所需的 SQL 脚本：

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

Returns:

返回：

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

One DROP MATERIALIZED VIEWstatement can take care of multiple materialized views. You may need to add CASCADEat the end if you have nested views.

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

Inspect the resulting DDL script to be sure before executing it. Are you sure you want to drop allMVs from all schemas in the db? And do you have the required privileges to do so? (Currently there are no materialized views in a fresh standard installation.)

在执行之前检查生成的 DDL 脚本以确保它。您确定要从数据库中的所有模式中删除所有MV 吗？您是否拥有这样做所需的权限？（目前在全新的标准安装中没有物化视图。）

---

---

While using the standard interactive terminal psql, you can use the meta-command:

使用标准交互式终端时psql，您可以使用元命令：

\dm

Executes this query on the server:

在服务器上执行此查询：

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;

Which can be reduced to:

可以简化为：

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;

回答by durbin510

This would be easier if you want to get a full list with the DROP statement in front of each view:

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

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

回答by Stefan

This an answer is based on the answer from Erwin Brandstetter. The version below adds a specific schema name to only retrieve the materialized views from a defined schema. The Cascasde also drops dependencies on the materialized views from that schema. Be careful with that.

这个答案基于Erwin Brandstetter的答案。下面的版本添加了一个特定的模式名称，以仅从定义的模式中检索物化视图。Cascasde 还从该模式中删除了对物化视图的依赖。小心点。

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>>'