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

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