删除所有视图 PostgreSQL

Question

我如何使用查询从PostgreSQL中删除所有用户定义的视图？就像我们可以使用查询删除所有函数一样：

SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname 
       || '(' || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'my_messed_up_schema'  order by proname;

- Satish Sharma

Answer 1

删除特定模式中所有视图的脚本：

SELECT 'DROP VIEW ' || t.oid::regclass || ';' -- CASCADE?
FROM   pg_class t
JOIN   pg_namespace n ON n.oid = t.relnamespace
WHERE  t.relkind = 'v'
AND    n.nspname = 'my_messed_up_schema -- select by schema(s)
ORDER  BY 1;

将t.oid::regclass转换为regclass类型可以避免SQLi，因为非法名称会自动引用。您也可以使用quote_ident()。

您的示例本质上是不安全的。

立即执行正确操作：

DO
$$
DECLARE
   sql text;
BEGIN
   SELECT INTO sql
          string_agg('DROP VIEW ' || t.oid::regclass || ';', ' ')  -- CASCADE?
   FROM   pg_class t
   JOIN   pg_namespace n ON n.oid = t.relnamespace
   WHERE  t.relkind = 'v'
   AND    n.nspname = 'my_messed_up_schema';

   IF sql IS NOT NULL THEN
      -- RAISE NOTICE '%', sql;  -- to debug
      EXECUTE sql;
   ELSE
      RAISE NOTICE 'No views found. Nothing dropped.';
   END IF;
END
$$

DO指令需要PostgreSQL 9.0或更高版本。 IF语句可以避免没有找到视图时出现异常。

如果您的视图引用其他视图，则必须添加关键字CASCADE，或按照从上到下的层次顺序删除它们。

在删除之前，一定要检查好要删除哪些内容，否则可能会让自己陷入麻烦。如果不确定，请使用事务功能，在删除操作之前进行一个提交或回滚操作，以确保一切都正常。

BEGIN;
DO$$
  ...
$$;

-- check ..

ROLLBACK; -- if something wrong
COMMIT; -- else

请注意，在plpgsql块内部，您无法COMMIT或ROLLBACK。只能在外部执行。

- Erwin Brandstetter

---

只要有视图需要删除，它就能正常工作，但如果没有视图，则会导致错误。我该如何解决这个问题？你使用的是哪种形式的 execute？在手册中，我只发现它与 prepare 结合使用... - yankee

@yankee：这是DO块内的plpgsql代码，因此不是执行准备语句的SQL命令。我添加了一个捕获异常的版本。在此处查找相关问题。 - Erwin Brandstetter

Answer 2

使用表pg_class。

需要relkind = 'v'

- Ihor Romanchenko

---

如何从pg_class中退出系统视图。我只想要属于特定模式的视图。 - Satish Sharma

@SatishSharma pg_class c JOIN pg_namespace ns ON c.relnamespace = ns.oid WHERE ns.nspname = 'my_messed_up_schema' 和你的例子一样。 - Ihor Romanchenko