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');
10
11create table test2 (id serial, t text);
12insert into test2 (t) values ('A');
13insert into test2 (t) values ('B');
14insert into test2 (t) values ('C');
15insert into test2 (t) values ('D');
16insert into test2 (t) values ('E');
17
18create view v_test1
19as select 'v_'||t from test1;
20
21--
22-- Test COPY table TO
23--
24copy test1 to stdout;
25--
26-- This should fail
27--
28copy v_test1 to stdout;
29--
30-- Test COPY (select) TO
31--
32copy (select t from test1 where id=1) to stdout;
33--
34-- Test COPY (select for update) TO
35--
36copy (select t from test1 where id=3 for update) to stdout;
37--
38-- This should fail
39--
40copy (select t into temp test3 from test1 where id=3) to stdout;
41--
42-- This should fail
43--
44copy (select * from test1) from stdin;
45--
46-- This should fail
47--
48copy (select * from test1) (t,id) to stdout;
49--
50-- Test JOIN
51--
52copy (select * from test1 join test2 using (id)) to stdout;
53--
54-- Test UNION SELECT
55--
56copy (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) to stdout;
57--
58-- Test subselect
59--
60copy (select * from (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) t1) to stdout;
61--
62-- Test headers, CSV and quotes
63--
64copy (select t from test1 where id = 1) to stdout csv header force quote t;
65--
66-- Test psql builtins, plain table
67--
68\copy test1 to stdout
69--
70-- This should fail
71--
72\copy v_test1 to stdout
73--
74-- Test \copy (select ...)
75--
76\copy (select "id",'id','id""'||t,(id + 1)*id,t,"test1"."t" from test1 where id=3) to stdout
77--
78-- Drop everything
79--
80drop table test2;
81drop view v_test1;
82drop table test1;
83
84-- psql handling of COPY in multi-command strings
85copy (select 1) to stdout\; select 1/0;	-- row, then error
86select 1/0\; copy (select 1) to stdout; -- error only
87copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3
88
89create table test3 (c int);
90select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
911
92\.
932
94\.
95select * from test3;
96drop table test3;
97