Re: PSQL Should \sv & \ev work with materialized views? - Mailing list pgsql-hackers

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