How to drop materialized views using EXECUTE statement in PostgreSQL

Question

My database is PostgreSQL (9.3.5).

We have more than 500 materialized views and want to drop some of them.
For that we are fetching from those materialized views from pg_class:

FOR MVNAME IN (SELECT relname
            FROM pg_class
            WHERE relkind='m')

LOOP
   EXECUTE 'DROP MATERIALIZED VIEW '||MVNAME||'';
END LOOP;

At executing time it's giving us an error:

ERROR: "dimension" is not a materialized view
SQL state: 42809

Hint: Use DROP FOREIGN TABLE to remove a foreign table.
Context: SQL statement "DROP MATERIALIZED VIEW dimension"
PL/pgSQL function test_drop_dims() line 14 at EXECUTE statement

We can drop one materialized view using:

DROP MATERIALIZED VIEW MVNAME;

But we can't drop multiple materialized views using EXECUTE statement.

score 2 · Accepted Answer · edited May 23 '17 at 11:50

The error message indicates that you have non-standard names created with double-quoting, like "Dimension" (mixed case). You need to quote and escape identifiers properly in dynamic SQL. Not only because it doesn't work any other way, also to avoid SQL injection.
Plus, you may have to schema-qualify names. Details:

Table name as a PostgreSQL function parameter

Also, you could drop multiple MV's at once and don't need a loop this way:

EXECUTE (
   SELECT 'DROP MATERIALIZED VIEW ' || string_agg(oid::regclass::text, ', ')
   FROM   pg_class
   WHERE  relkind = 'm'
   );

Careful with this! It drops all materialized views in all schemas of your current database. You may want to double-check first.

Note how I am using oid::regclass::text, not quote_ident(relname). That also covers the schema name automatically. Detailed explantaion in the provided link.

@a_horse_with_no_name: If you execute a statement `DROP TABLE dimension` or `DROP TABLE Dimension` (same thing), how would the system know you really meant `"Dimension"` or `"diMenSion"`? The error message reports what you entered, folded to lower-case unless double-quoted, that's all. See: http://sqlfiddle.com/#!15/d41d8/4009 — Erwin Brandstetter, Nov 25 '14 at 15:54

How to drop materialized views using EXECUTE statement in PostgreSQL

1 Answers1