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