v3-0001-COPY-materialized_view-TO.patch

From ba91ac0d9403f304d6c72683ca2c471e2c3c3ecc Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 3 Apr 2025 16:45:48 +0800
Subject: [PATCH v3 1/1] COPY materialized_view TO

generally `COPY table TO` is faster than `COPY (query)`.
since populated materialized view have physical storage, so
this can use table_beginscan, table_endscan to scan a table.

context: https://postgr.es/m/8967.1353167301@sss.pgh.pa.us
context: https://www.postgresql.org/message-id/flat/20121116162558.90150%40gmx.com
discussion: https://postgr.es/m/CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=yg@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5533/
---
 doc/src/sgml/ref/copy.sgml         | 14 ++++++++------
 src/backend/commands/copyto.c      | 13 ++++++++-----
 src/test/regress/expected/copy.out | 11 +++++++++++
 src/test/regress/sql/copy.sql      |  8 ++++++++
 4 files changed, 35 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..c855e98f757 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -520,12 +520,14 @@ COPY <replaceable class="parameter">count</replaceable>
   <title>Notes</title>
 
    <para>
-    <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
-    class="parameter">table</replaceable> TO</literal> copies
-    the same rows as <literal>SELECT * FROM ONLY <replaceable
-    class="parameter">table</replaceable></literal>.
+    <command>COPY TO</command> can be used with plain
+    tables and populated materialized views.
+    For example,
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as
+    <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    However it doesn't directly support other relation types,
+    such as partitioned tables, inheritance child tables, or views.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
     dump all of the rows in an inheritance hierarchy, partitioned table,
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..9233cbcecb4 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -653,11 +653,14 @@ BeginCopyTo(ParseState *pstate,
 							RelationGetRelationName(rel)),
 					 errhint("Try the COPY (SELECT ...) TO variant.")));
 		else if (rel->rd_rel->relkind == RELKIND_MATVIEW)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from materialized view \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			if (!RelationIsPopulated(rel))
+				ereport(ERROR,
+						errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						errmsg("cannot copy from unpopulated materialized view \"%s\"",
+								RelationGetRelationName(rel)),
+						errhint("Use the REFRESH MATERIALIZED VIEW command."));
+		}
 		else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 06bae8c61ae..39af90f5292 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -338,3 +338,14 @@ create foreign table copytest_foreign_table (a int) server copytest_server;
 copy copytest_foreign_table from stdin (freeze);
 ERROR:  cannot perform COPY FREEZE on a foreign table
 rollback;
+-- COPY TO with materialized view
+CREATE MATERIALIZED VIEW matview1 AS SELECT 1 as id;
+CREATE MATERIALIZED VIEW matview2 AS SELECT 1 as id WITH NO DATA;
+COPY matview1(id) TO stdout WITH (header);
+id
+1
+COPY matview2 TO stdout WITH (header);
+ERROR:  cannot copy from unpopulated materialized view "matview2"
+HINT:  Use the REFRESH MATERIALIZED VIEW command.
+DROP MATERIALIZED VIEW matview1;
+DROP MATERIALIZED VIEW matview2;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 3009bdfdf89..5df65924f8b 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -366,3 +366,11 @@ copy copytest_foreign_table from stdin (freeze);
 1
 \.
 rollback;
+
+-- COPY TO with materialized view
+CREATE MATERIALIZED VIEW matview1 AS SELECT 1 as id;
+CREATE MATERIALIZED VIEW matview2 AS SELECT 1 as id WITH NO DATA;
+COPY matview1(id) TO stdout WITH (header);
+COPY matview2 TO stdout WITH (header);
+DROP MATERIALIZED VIEW matview1;
+DROP MATERIALIZED VIEW matview2;
-- 
2.34.1