1--
2-- Test cases for COPY (select) TO
3--
4create table test1 (id serial, t text);
5insert into test1 (t) values ('a');
6insert into test1 (t) values ('b');
7insert into test1 (t) values ('c');
8insert into test1 (t) values ('d');
9insert into test1 (t) values ('e');
10create table test2 (id serial, t text);
11insert into test2 (t) values ('A');
12insert into test2 (t) values ('B');
13insert into test2 (t) values ('C');
14insert into test2 (t) values ('D');
15insert into test2 (t) values ('E');
16create view v_test1
17as select 'v_'||t from test1;
18--
19-- Test COPY table TO
20--
21copy test1 to stdout;
221	a
232	b
243	c
254	d
265	e
27--
28-- This should fail
29--
30copy v_test1 to stdout;
31ERROR:  cannot copy from view "v_test1"
32HINT:  Try the COPY (SELECT ...) TO variant.
33--
34-- Test COPY (select) TO
35--
36copy (select t from test1 where id=1) to stdout;
37a
38--
39-- Test COPY (select for update) TO
40--
41copy (select t from test1 where id=3 for update) to stdout;
42c
43--
44-- This should fail
45--
46copy (select t into temp test3 from test1 where id=3) to stdout;
47ERROR:  COPY (SELECT INTO) is not supported
48--
49-- This should fail
50--
51copy (select * from test1) from stdin;
52ERROR:  syntax error at or near "from"
53LINE 1: copy (select * from test1) from stdin;
54                                   ^
55--
56-- This should fail
57--
58copy (select * from test1) (t,id) to stdout;
59ERROR:  syntax error at or near "("
60LINE 1: copy (select * from test1) (t,id) to stdout;
61                                   ^
62--
63-- Test JOIN
64--
65copy (select * from test1 join test2 using (id)) to stdout;
661	a	A
672	b	B
683	c	C
694	d	D
705	e	E
71--
72-- Test UNION SELECT
73--
74copy (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) to stdout;
75a
76v_a
77v_b
78v_c
79v_d
80v_e
81--
82-- Test subselect
83--
84copy (select * from (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) t1) to stdout;
85a
86v_a
87v_b
88v_c
89v_d
90v_e
91--
92-- Test headers, CSV and quotes
93--
94copy (select t from test1 where id = 1) to stdout csv header force quote t;
95t
96"a"
97--
98-- Test psql builtins, plain table
99--
100\copy test1 to stdout
1011	a
1022	b
1033	c
1044	d
1055	e
106--
107-- This should fail
108--
109\copy v_test1 to stdout
110ERROR:  cannot copy from view "v_test1"
111HINT:  Try the COPY (SELECT ...) TO variant.
112--
113-- Test \copy (select ...)
114--
115\copy (select "id",'id','id""'||t,(id + 1)*id,t,"test1"."t" from test1 where id=3) to stdout
1163	id	id""c	12	c	c
117--
118-- Drop everything
119--
120drop table test2;
121drop view v_test1;
122drop table test1;
123-- psql handling of COPY in multi-command strings
124copy (select 1) to stdout\; select 1/0;	-- row, then error
1251
126ERROR:  division by zero
127select 1/0\; copy (select 1) to stdout; -- error only
128ERROR:  division by zero
129copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3
1301
1312
132 ?column?
133----------
134        3
135(1 row)
136
137create table test3 (c int);
138select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
139 ?column?
140----------
141        1
142(1 row)
143
144select * from test3;
145 c
146---
147 1
148 2
149(2 rows)
150
151drop table test3;
152