CODEWITHSUNDEEP
sqlpostgresqlviewpsqlmaterialized-views
riders994
riders994

Reputation: 1306

Drop View Regardless of Type (Postgres)

I'm trying to convert a materialized view to a view. Due to how our schemas are deployed, I have to come up with a SQL script that drops whatever is at the table name, and re-creates the view. However, I receive an error upon re-running this code that the view is not a materialized view, and I need to use DROP VIEW instead. Is there a way to get around this? Will DROP VIEW drop a materialized view as well?

Upvotes: 0

Views: 52

Answers (1)

klin
klin

Reputation: 121634

You can use the function:

create or replace function drop_any_view(regclass)
returns void language plpgsql as $$
begin
    execute format(
        'drop %s view %s', 
            case (select relkind from pg_class where oid = $1)
            when 'm' then 'materialized'
            else '' end,
            $1
        );
end $$;

The function drops a view regardless it is materialized or not.

select drop_any_view('my_view');

Upvotes: 1

Related Questions