1

I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW.

DROP VIEW IF EXISTS myview;

throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I am looking for is drop statement (or function) that does not care about if it has to drop a MATERIALIZED VIEW or a VIEW.

(hint: there seems to be neither drop nor drop-view tag, therefore drop-table)

2

My solution at this point is a function

CREATE OR REPLACE FUNCTION dropview(IN view_schema TEXT, IN view2drop TEXT)
RETURNS void
LANGUAGE plpgsql
AS 
$$
DECLARE drop_stmnt VARCHAR;
BEGIN
SELECT 
    CASE
        WHEN relkind = 'm' THEN 'MATERIALIZED VIEW'
        WHEN relkind = 'v' THEN 'VIEW'
    END
FROM pg_class
WHERE relname = view2drop
INTO drop_stmnt;

EXECUTE 'DROP ' || drop_stmnt || ' IF EXISTS ' || view_schema ||'.'|| view2drop || ';';
END;
$$;

so that with

SELECT dropview('myschema', 'myview');

doesn't have to care about MATERIALIZED VIEW or VIEW.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.