删除所有视图PostgreSQL

本文介绍了删除所有视图PostgreSQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

How I can Delete All User Defined Views From PostgreSQL using a Query? Like we can delete All functions using query :

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;

推荐答案

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

Script for deleting all views in a certain schema:

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 ）可防止，因为否则会自动引用非法名称。您还可以使用 quote_ident（）。

The cast to regclass (t.oid::regclass) prevents SQLi, because otherwise illegal names are quoted automatically. You could also use quote_ident().

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

Your example is inherently unsafe.

立即：

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或更高版本。

DO requires PostgreSQL 9.0 or later.

如果找不到视图，则 IF 构造可避免出现异常。

The IF construct avoids an exception if no views are found.

如果您有引用其他视图的视图，则必须添加关键字或按从上到下的层次结构顺序放置视图。

If you have views referencing other views, you'll have to add the keyword CASCADE or drop views in their hierarchical order from top to bottom.

始终在执行操作之前先检查要删除的内容，否则您可能会自己uke核。如果不确定，请开始交易，投下炸弹，检查一切是否良好，然后提交或回滚。

Always check what you are going to drop before you do it, or you might nuke yourself. If you are unsure, start a transaction, drop the bomb, check if all is good and then either commit or roll back.

BEGIN;
DO$$
  ...
$$;

-- check ..

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

请注意，您不能 COMMIT 或在plpgsql块中 ROLLBACK inside 。只能在外面。

Note that you cannot COMMIT or ROLLBACK inside the plpgsql block. Only outside.

这篇关于删除所有视图PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！