CODEWITHSUNDEEP
postgresqlviewdynamic-sqlsql-drop
Satish Sharma
Satish Sharma

Reputation: 3294

DROP All Views 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;

Upvotes: 3

Views: 3233

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656962

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.

Upvotes: 6

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28551

Use table pg_class.

You need relkind = 'v'

Upvotes: 0

Related Questions