1--
2-- Test cases for COPY (INSERT/UPDATE/DELETE) TO
3--
4create table copydml_test (id serial, t text);
5insert into copydml_test (t) values ('a');
6insert into copydml_test (t) values ('b');
7insert into copydml_test (t) values ('c');
8insert into copydml_test (t) values ('d');
9insert into copydml_test (t) values ('e');
10
11--
12-- Test COPY (insert/update/delete ...)
13--
14copy (insert into copydml_test (t) values ('f') returning id) to stdout;
15copy (update copydml_test set t = 'g' where t = 'f' returning id) to stdout;
16copy (delete from copydml_test where t = 'g' returning id) to stdout;
17
18--
19-- Test \copy (insert/update/delete ...)
20--
21\copy (insert into copydml_test (t) values ('f') returning id) to stdout;
22\copy (update copydml_test set t = 'g' where t = 'f' returning id) to stdout;
23\copy (delete from copydml_test where t = 'g' returning id) to stdout;
24
25-- Error cases
26copy (insert into copydml_test default values) to stdout;
27copy (update copydml_test set t = 'g') to stdout;
28copy (delete from copydml_test) to stdout;
29
30create rule qqq as on insert to copydml_test do instead nothing;
31copy (insert into copydml_test default values) to stdout;
32drop rule qqq on copydml_test;
33create rule qqq as on insert to copydml_test do also delete from copydml_test;
34copy (insert into copydml_test default values) to stdout;
35drop rule qqq on copydml_test;
36create rule qqq as on insert to copydml_test do instead (delete from copydml_test; delete from copydml_test);
37copy (insert into copydml_test default values) to stdout;
38drop rule qqq on copydml_test;
39create rule qqq as on insert to copydml_test where new.t <> 'f' do instead delete from copydml_test;
40copy (insert into copydml_test default values) to stdout;
41drop rule qqq on copydml_test;
42
43create rule qqq as on update to copydml_test do instead nothing;
44copy (update copydml_test set t = 'f') to stdout;
45drop rule qqq on copydml_test;
46create rule qqq as on update to copydml_test do also delete from copydml_test;
47copy (update copydml_test set t = 'f') to stdout;
48drop rule qqq on copydml_test;
49create rule qqq as on update to copydml_test do instead (delete from copydml_test; delete from copydml_test);
50copy (update copydml_test set t = 'f') to stdout;
51drop rule qqq on copydml_test;
52create rule qqq as on update to copydml_test where new.t <> 'f' do instead delete from copydml_test;
53copy (update copydml_test set t = 'f') to stdout;
54drop rule qqq on copydml_test;
55
56create rule qqq as on delete to copydml_test do instead nothing;
57copy (delete from copydml_test) to stdout;
58drop rule qqq on copydml_test;
59create rule qqq as on delete to copydml_test do also insert into copydml_test default values;
60copy (delete from copydml_test) to stdout;
61drop rule qqq on copydml_test;
62create rule qqq as on delete to copydml_test do instead (insert into copydml_test default values; insert into copydml_test default values);
63copy (delete from copydml_test) to stdout;
64drop rule qqq on copydml_test;
65create rule qqq as on delete to copydml_test where old.t <> 'f' do instead insert into copydml_test default values;
66copy (delete from copydml_test) to stdout;
67drop rule qqq on copydml_test;
68
69-- triggers
70create function qqq_trig() returns trigger as $$
71begin
72if tg_op in ('INSERT', 'UPDATE') then
73    raise notice '% %', tg_op, new.id;
74    return new;
75else
76    raise notice '% %', tg_op, old.id;
77    return old;
78end if;
79end
80$$ language plpgsql;
81create trigger qqqbef before insert or update or delete on copydml_test
82    for each row execute procedure qqq_trig();
83create trigger qqqaf after insert or update or delete on copydml_test
84    for each row execute procedure qqq_trig();
85
86copy (insert into copydml_test (t) values ('f') returning id) to stdout;
87copy (update copydml_test set t = 'g' where t = 'f' returning id) to stdout;
88copy (delete from copydml_test where t = 'g' returning id) to stdout;
89
90drop table copydml_test;
91drop function qqq_trig();
92