Re: PSQL Should \sv & \ev work with materialized views?

On 2024-03-29 04:27 +0100, Isaac Morland wrote:
> On Thu, 28 Mar 2024 at 20:38, Erik Wienhold  wrote:
> 
> 
> > Of course the problem with using DROP and CREATE is that indexes and
> > privileges (anything else?) must also be restored.  I haven't bothered
> > with that yet.
> >
> 
> Not just those — also anything that depends on the matview, such as views
> and other matviews.

Right.  But you'd run into the same issue for a regular view if you use
\ev and add  DROP VIEW myview CASCADE  which may be necessary if you
want to change columns names and/or types.  Likewise, you'd have to
manually change  DROP MATERIALIZED VIEW  and add the CASCADE option to
lose dependent objects.

I think implementing  CREATE OR REPLACE MATERIALIZED VIEW  has more
value.  But the semantics have to be defined first.  I guess it has to
behave like  CREATE OR REPLACE VIEW  in that it only allows changing the
query without altering column names and types.

We could also implement \sv so that it only prints  CREATE MATERIALIZED
VIEW  and change \ev to not work with matviews.  Both commands use
get_create_object_cmd to populate the query buffer, so you get \ev for
free when changing \sv.

-- 
Erik