RE: BUG #15384: dropping views and materialized views

Hi Merlin

Thanks for the speedy response.

Don't know that I agree with your assessment of the issue though...

Think it would be cool if this could maybe be addressed in the future?

In the meantime I will see if I can read the metadata in the db to determine if a view is materialized or not and then
buildthe applicable 'drop' statement from there.
 

Best regards,
Terence Zekveld
Developer

EOH Roads & Highways 
A division of EOH Industrial Technologies (Pty) Ltd
70 Regency Drive, Route 21 Corporate Park, Centurion

Tel: +27 (12) 346 1255 | Mobile: +27 (79) 696 5363 
terence.zekveld@eoh.com    |    www.eoh.co.za                                                            

Consulting | Technology | Outsourcing





-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com] 
Sent: 14 September 2018 02:55 PM
To: Terence Zekveld; pgsql-bugs@lists.postgresql.org
Subject: 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