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