Re: BUG #15384: dropping views and materialized views

On Fri, Sep 14, 2018 at 4:41 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      15384
> Logged by:          Terence Zekveld
> Email address:      terence.zekveld@eoh.com
> PostgreSQL version: 9.6.1
> Operating system:   Windows
> Description:
>
> Sometimes we change a view to a materialized view.
>
> We have a general upgrading script to update all our postgres db's to keep
> them in sync.
>
> So I like to add this to my general upgrading script before creating the
> materialized view:
>
> DROP VIEW IF EXISTS theschema.theviewname;                             --
> for in case this db still has the 'un'materialized view
> DROP MATERIALIZED VIEW IF EXISTS theschema.theviewname;  -- for in case this
> db already has an older version of the materialized view
> CREATE MATERIALIZED VIEW theschema.theviewname AS ...
>
> But either the 1st or the 2nd DROP functions throw an error, either
> "theschema.theviewname is not a view" or "theschema.theviewname is not a
> materialized view".
>
> I would think these errors are not relevant when using the "IF EXISTS"
> option, i.e. it should execute both, 'skipping' the one that refers to the
> incorrect type of view...

One option here is to wrap those commands in a DO block and trap the
error.  I consider this to be SOP for standardized schema refresh
scripts.

merlin