Database 是否有一个 postgres 命令来列出删除所有materialized视图

纯SQL

全部显示:

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

在从regclass到text的强制转换中，根据您当前的search_path，名称在需要的地方会自动用双引号引起来并进行架构限定.

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

m = materialized view

对于drop all，您可以使用以下查询生成所需的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脚本以确保它.是否确实要从数据库中的所有架构中删除all个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;