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
