DROP All Views PostgreSQL

Question

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;

Answer 1

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;

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 it right away:

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 requires PostgreSQL 9.0 or later.

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.

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

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