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
>From efb5e37d90b668011307b602655f28455d700635 Mon Sep 17 00:00:00 2001 From: Erik Wienhold <e...@ewie.name> Date: Fri, 29 Mar 2024 01:08:35 +0100 Subject: [PATCH v1] psql: \ev and \sv for matviews CREATE OR REPLACE is not available for materialized views so DROP and CREATE them inside a transaction. Use meta command \; to compose the query buffer without sending it to the server. TODO: Re-create indexes and privileges which are currently lost by relying on DROP and CREATE. --- src/bin/psql/command.c | 29 ++++++++++++++++++++--------- 1 file changed, 20 insertions(+), 9 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 9b0fa041f7..f40c1d7f99 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -5575,19 +5575,22 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid, char *reloptions = PQgetvalue(res, 0, 4); char *checkoption = PQgetvalue(res, 0, 5); - /* - * If the backend ever supports CREATE OR REPLACE - * MATERIALIZED VIEW, allow that here; but as of today it - * does not, so editing a matview definition in this way - * is impossible. - */ switch (relkind[0]) { -#ifdef NOT_USED case RELKIND_MATVIEW: - appendPQExpBufferStr(buf, "CREATE OR REPLACE MATERIALIZED VIEW "); + /* + * Allow editing a matview via separate DROP and + * CREATE statement inside a transaction. Use meta + * command \; to write more than one statement to + * the query buffer without sending it to the server. + */ + appendPQExpBufferStr(buf, "BEGIN \\;\n"); + appendPQExpBufferStr(buf, "DROP MATERIALIZED VIEW "); + appendPQExpBuffer(buf, "%s.", fmtId(nspname)); + appendPQExpBufferStr(buf, fmtId(relname)); + appendPQExpBufferStr(buf, " \\;\n"); + appendPQExpBufferStr(buf, "CREATE MATERIALIZED VIEW "); break; -#endif case RELKIND_VIEW: appendPQExpBufferStr(buf, "CREATE OR REPLACE VIEW "); break; @@ -5625,6 +5628,14 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid, if (checkoption && checkoption[0] != '\0') appendPQExpBuffer(buf, "\n WITH %s CHECK OPTION", checkoption); + + /* Matview is re-created inside a transaction. */ + if (relkind[0] == RELKIND_MATVIEW) + /* + * TODO Also re-create indexes and privileges that are + * lost by using DROP and CREATE. + */ + appendPQExpBufferStr(buf, "\n WITH DATA \\;\nCOMMIT"); } break; } -- 2.44.0