4 files changed, 212 insertions, 3 deletions
diff --git a/src/test/regress/expected/copyselect.out b/src/test/regress/expected/copyselect.out
new file mode 100644
index 00000000000..c42bad143e4
--- /dev/null
+++ b/src/test/regress/expected/copyselect.out
@@ -0,0 +1,126 @@
+--
+-- Test cases for COPY (select) TO
+--
+create table test1 (id serial, t text);
+NOTICE:  CREATE TABLE will create implicit sequence "test1_id_seq" for serial column "test1.id"
+insert into test1 (t) values ('a');
+insert into test1 (t) values ('b');
+insert into test1 (t) values ('c');
+insert into test1 (t) values ('d');
+insert into test1 (t) values ('e');
+create table test2 (id serial, t text);
+NOTICE:  CREATE TABLE will create implicit sequence "test2_id_seq" for serial column "test2.id"
+insert into test2 (t) values ('A');
+insert into test2 (t) values ('B');
+insert into test2 (t) values ('C');
+insert into test2 (t) values ('D');
+insert into test2 (t) values ('E');
+create view v_test1
+as select 'v_'||t from test1;
+--
+-- Test COPY table TO
+--
+copy test1 to stdout;
+1	a
+2	b
+3	c
+4	d
+5	e
+--
+-- This should fail
+--
+copy v_test1 to stdout;
+ERROR:  cannot copy from view "v_test1"
+HINT:  Try the COPY (SELECT ...) TO variant.
+--
+-- Test COPY (select) TO
+--
+copy (select t from test1 where id=1) to stdout;
+a
+--
+-- Test COPY (select for update) TO
+--
+copy (select t from test1 where id=3 for update) to stdout;
+c
+--
+-- This should fail
+--
+copy (select t into temp test3 from test1 where id=3) to stdout;
+ERROR:  COPY (SELECT INTO) is not supported
+--
+-- This should fail
+--
+copy (select * from test1) from stdin;
+ERROR:  syntax error at or near "from"
+LINE 1: copy (select * from test1) from stdin;
+                                   ^
+--
+-- This should fail
+--
+copy (select * from test1) (t,id) to stdout;
+ERROR:  syntax error at or near "("
+LINE 1: copy (select * from test1) (t,id) to stdout;
+                                   ^
+--
+-- Test JOIN
+--
+copy (select * from test1 join test2 using (id)) to stdout;
+1	a	A
+2	b	B
+3	c	C
+4	d	D
+5	e	E
+--
+-- Test UNION SELECT
+--
+copy (select t from test1 where id = 1 UNION select * from v_test1) to stdout;
+a
+v_a
+v_b
+v_c
+v_d
+v_e
+--
+-- Test subselect
+--
+copy (select * from (select t from test1 where id = 1 UNION select * from v_test1) t1) to stdout;
+a
+v_a
+v_b
+v_c
+v_d
+v_e
+--
+-- Test headers, CSV and quotes
+--
+copy (select t from test1 where id = 1) to stdout csv header force quote t;
+t
+"a"
+--
+-- Test psql builtins, plain table
+--
+\copy test1 to stdout
+1	a
+2	b
+3	c
+4	d
+5	e
+--
+-- This should fail
+--
+\copy v_test1 to stdout
+ERROR:  cannot copy from view "v_test1"
+HINT:  Try the COPY (SELECT ...) TO variant.
+\copy: ERROR:  cannot copy from view "v_test1"
+HINT:  Try the COPY (SELECT ...) TO variant.
+-- 
+-- Test \copy (select ...)
+--
+\copy (select "id",'id','id""'||t,(id + 1)*id,t,"test1"."t" from test1 where id=3) to stdout
+3	id	id""c	12	c	c
+--
+-- Drop everything
+--
+drop table test2;
+drop view v_test1;
+drop table test1;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index d675c07ff18..f13ea4792a3 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -1,6 +1,6 @@
 # ----------
 # The first group of parallel test
-# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.34 2006/08/12 02:52:06 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.35 2006/08/30 23:34:22 tgl Exp $
 # ----------
 test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric
 
@@ -34,7 +34,7 @@ test: create_function_2
 # execute two copy tests parallel, to check that copy itself
 # is concurrent safe.
 # ----------
-test: copy
+test: copy copyselect
 
 # ----------
 # The third group of parallel test
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 1bb8742da6c..2d44e585d38 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.32 2006/08/12 02:52:06 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.33 2006/08/30 23:34:22 tgl Exp $
 # This should probably be in an order similar to parallel_schedule.
 test: boolean
 test: char
@@ -43,6 +43,7 @@ test: create_type
 test: create_table
 test: create_function_2
 test: copy
+test: copyselect
 test: constraints
 test: triggers
 test: create_misc
diff --git a/src/test/regress/sql/copyselect.sql b/src/test/regress/sql/copyselect.sql
new file mode 100644
index 00000000000..c2526487c8c
--- /dev/null
+++ b/src/test/regress/sql/copyselect.sql
@@ -0,0 +1,82 @@
+--
+-- Test cases for COPY (select) TO
+--
+create table test1 (id serial, t text);
+insert into test1 (t) values ('a');
+insert into test1 (t) values ('b');
+insert into test1 (t) values ('c');
+insert into test1 (t) values ('d');
+insert into test1 (t) values ('e');
+
+create table test2 (id serial, t text);
+insert into test2 (t) values ('A');
+insert into test2 (t) values ('B');
+insert into test2 (t) values ('C');
+insert into test2 (t) values ('D');
+insert into test2 (t) values ('E');
+
+create view v_test1
+as select 'v_'||t from test1;
+
+--
+-- Test COPY table TO
+--
+copy test1 to stdout;
+--
+-- This should fail
+--
+copy v_test1 to stdout;
+--
+-- Test COPY (select) TO
+--
+copy (select t from test1 where id=1) to stdout;
+--
+-- Test COPY (select for update) TO
+--
+copy (select t from test1 where id=3 for update) to stdout;
+--
+-- This should fail
+--
+copy (select t into temp test3 from test1 where id=3) to stdout;
+--
+-- This should fail
+--
+copy (select * from test1) from stdin;
+--
+-- This should fail
+--
+copy (select * from test1) (t,id) to stdout;
+--
+-- Test JOIN
+--
+copy (select * from test1 join test2 using (id)) to stdout;
+--
+-- Test UNION SELECT
+--
+copy (select t from test1 where id = 1 UNION select * from v_test1) to stdout;
+--
+-- Test subselect
+--
+copy (select * from (select t from test1 where id = 1 UNION select * from v_test1) t1) to stdout;
+--
+-- Test headers, CSV and quotes
+--
+copy (select t from test1 where id = 1) to stdout csv header force quote t;
+--
+-- Test psql builtins, plain table
+--
+\copy test1 to stdout
+--
+-- This should fail
+--
+\copy v_test1 to stdout
+-- 
+-- Test \copy (select ...)
+--
+\copy (select "id",'id','id""'||t,(id + 1)*id,t,"test1"."t" from test1 where id=3) to stdout
+--
+-- Drop everything
+--
+drop table test2;
+drop view v_test1;
+drop table test1;