Drop normal or materialized view in Postgresql 9.3 in one query

Question

I’ve got a view in my PostgreSQL, which can be both normal or materialized, depending on some circumstances. I'm trying to write a query that would drop the view for sure and with no errors no matter what type it has got at the moment. However, this does not seem to be easy. When I try to use the following code, I get an error:

DROP VIEW IF EXISTS {{ schema }}.{{ viewName }};
DROP MATERIALIZED VIEW IF EXISTS {{ schema }}.{{ viewName }};

SQLSTATE[42809]: Wrong object type: 7 ERROR:  "{{ viewName }}" is not a view  
HINT:  Use DROP MATERIALIZED VIEW to remove a materialized view.

Thus, it looks like ‘IF EXISTS’ results true in the first row when the view is materialized, DROP command launches, but fails because the type of the view is wrong (it is not normal). Could anyone suggest a universal workaround that would both work for both materialized and normal view?

score 1 · Answer 1 · answered Oct 22 '13 at 17:24

There isn't. Or not easily, anyway... As Postgres is reporting, the two are different animals.

The reason if exists kicks in is, I think, due to the fact that this all resides in pg_class. If you create a test table, and try to run drop view if exists test, you'll get a similar error.

You could, as a not so great workaround, generate a dynamic SQL statement in a DO block, after checking the type of the entity in pg_catalog.pg_class to determin the precise type of what you're dealing with (table, view, mat view, etc.).

score 1 · Answer 2 · answered Aug 17 '20 at 08:46

Here is a PLSQL function to reach your needs:


CREATE OR REPLACE FUNCTION drop_any_type_of_view_if_exists(IN _viewname text)
RETURNS VOID AS
$$  
BEGIN
    RAISE LOG 'Looking for (materialized) view named %', _viewname;
    IF EXISTS (SELECT matviewname from pg_matviews where schemaname = 'public' and matviewname = _viewname) THEN
        RAISE NOTICE 'DROP MATERIALIZED VIEW %', _viewname;
        EXECUTE 'DROP MATERIALIZED VIEW ' || quote_ident(_viewname); 
    ELSEIF EXISTS (SELECT viewname from pg_views where schemaname = 'public' and viewname = _viewname) THEN
        RAISE NOTICE 'DROP VIEW %', _viewname;
        EXECUTE 'DROP VIEW ' || quote_ident(_viewname);
    ELSE
        RAISE NOTICE 'NO VIEW % found', _viewname;
    END IF;
END;
$$ LANGUAGE plpgsql;

You can also call your created function with following syntax


SELECT drop_any_type_of_view_if_exists('v_my_view');

Drop normal or materialized view in Postgresql 9.3 in one query

2 Answers2