diff options
| -rw-r--r-- | doc/src/sgml/ref/copy.sgml | 20 | ||||
| -rw-r--r-- | src/backend/commands/copyto.c | 13 | ||||
| -rw-r--r-- | src/test/regress/expected/copy.out | 12 | ||||
| -rw-r--r-- | src/test/regress/sql/copy.sql | 9 |
4 files changed, 39 insertions, 15 deletions
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index df093da97c5..d6859276bed 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -520,16 +520,16 @@ 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>. - 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, - or view. + <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. + To copy all rows from such relations, use <literal>COPY (SELECT * FROM + <replaceable class="parameter">table</replaceable>) TO</literal>. </para> <para> diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c index 84a3f3879a8..f87e405351d 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..8d5a06563c4 100644 --- a/src/test/regress/expected/copy.out +++ b/src/test/regress/expected/copy.out @@ -338,3 +338,15 @@ 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; +-- Tests for COPY TO with materialized views. +-- COPY TO should fail for an unpopulated materialized view +-- but succeed for a populated one. +CREATE MATERIALIZED VIEW copytest_mv AS SELECT 1 AS id WITH NO DATA; +COPY copytest_mv(id) TO stdout WITH (header); +ERROR: cannot copy from unpopulated materialized view "copytest_mv" +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW copytest_mv; +COPY copytest_mv(id) TO stdout WITH (header); +id +1 +DROP MATERIALIZED VIEW copytest_mv; diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql index 3009bdfdf89..f0b88a23db8 100644 --- a/src/test/regress/sql/copy.sql +++ b/src/test/regress/sql/copy.sql @@ -366,3 +366,12 @@ copy copytest_foreign_table from stdin (freeze); 1 \. rollback; + +-- Tests for COPY TO with materialized views. +-- COPY TO should fail for an unpopulated materialized view +-- but succeed for a populated one. +CREATE MATERIALIZED VIEW copytest_mv AS SELECT 1 AS id WITH NO DATA; +COPY copytest_mv(id) TO stdout WITH (header); +REFRESH MATERIALIZED VIEW copytest_mv; +COPY copytest_mv(id) TO stdout WITH (header); +DROP MATERIALIZED VIEW copytest_mv; |