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