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 <ewie(at)ewie(dot)name> 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