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

On 2023-05-15 06:32 +0200, Kirk Wolak wrote:
> Personally I would appreciate it if \sv actually showed you the DDL.
> Oftentimes I will \ev something to review it, with syntax highlighting.

+1.  I was just reviewing some matviews and was surprised that psql
lacks commands to show their definitions.

But I think that it should be separate commands \sm and \em because we
already have commands \dm and \dv that distinguish between matviews and
views.

> This should not be that difficult.  Just looking for feedback.
> Admittedly \e is questionable, because you cannot really apply the changes.
> ALTHOUGH, I would consider that I could
> BEGIN;
> DROP MATERIALIZED VIEW ...;
> CREATE MATERIALIZED VIEW ...;
> 
> Which I had to do to change the WITH DATA so it creates with data when we
> reload our object.s

I think this could even be handled by optional modifiers, e.g. \em emits
CREATE MATERIALIZED VIEW ... WITH NO DATA and \emD emits WITH DATA.
Although I wouldn't mind manually changing WITH NO DATA to WITH DATA.

-- 
Erik

I wrote:
> On 2023-05-15 06:32 +0200, Kirk Wolak wrote:
> > Personally I would appreciate it if \sv actually showed you the DDL.
> > Oftentimes I will \ev something to review it, with syntax highlighting.
> 
> +1.  I was just reviewing some matviews and was surprised that psql
> lacks commands to show their definitions.
> 
> But I think that it should be separate commands \sm and \em because we
> already have commands \dm and \dv that distinguish between matviews and
> views.

Separate commands are not necessary because \ev and \sv already have a
(disabled) provision in get_create_object_cmd for when CREATE OR REPLACE
MATERIALIZED VIEW is available.  So I guess both commands should also
apply to matview.  The attached patch replaces that provision with a
transaction that drops and creates the matview.  This uses meta command
\; to put multiple statements into the query buffer without prematurely
sending those statements to the server.

Demo:

    => DROP MATERIALIZED VIEW IF EXISTS test;
    DROP MATERIALIZED VIEW
    => CREATE MATERIALIZED VIEW test AS SELECT s FROM generate_series(1, 10) s;
    SELECT 10
    => \sv test
    BEGIN \;
    DROP MATERIALIZED VIEW public.test \;
    CREATE MATERIALIZED VIEW public.test AS
     SELECT s
       FROM generate_series(1, 10) s(s)
     WITH DATA \;
    COMMIT
    =>

And \ev test works as well.

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.

-- 
Erik

On 2024-03-29 04:27 +0100, Isaac Morland wrote:
> On Thu, 28 Mar 2024 at 20:38, Erik Wienhold <ewie@ewie.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