1-- 2-- Test INSERT/UPDATE/DELETE RETURNING 3-- 4 5-- Simple cases 6 7CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42); 8 9INSERT INTO foo (f2,f3) 10 VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9) 11 RETURNING *, f1+f3 AS sum; 12 13SELECT * FROM foo; 14 15UPDATE foo SET f2 = lower(f2), f3 = DEFAULT RETURNING foo.*, f1+f3 AS sum13; 16 17SELECT * FROM foo; 18 19DELETE FROM foo WHERE f1 > 2 RETURNING f3, f2, f1, least(f1,f3); 20 21SELECT * FROM foo; 22 23-- Subplans and initplans in the RETURNING list 24 25INSERT INTO foo SELECT f1+10, f2, f3+99 FROM foo 26 RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan, 27 EXISTS(SELECT * FROM int4_tbl) AS initplan; 28 29UPDATE foo SET f3 = f3 * 2 30 WHERE f1 > 10 31 RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan, 32 EXISTS(SELECT * FROM int4_tbl) AS initplan; 33 34DELETE FROM foo 35 WHERE f1 > 10 36 RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan, 37 EXISTS(SELECT * FROM int4_tbl) AS initplan; 38 39-- Joins 40 41UPDATE foo SET f3 = f3*2 42 FROM int4_tbl i 43 WHERE foo.f1 + 123455 = i.f1 44 RETURNING foo.*, i.f1 as "i.f1"; 45 46SELECT * FROM foo; 47 48DELETE FROM foo 49 USING int4_tbl i 50 WHERE foo.f1 + 123455 = i.f1 51 RETURNING foo.*, i.f1 as "i.f1"; 52 53SELECT * FROM foo; 54 55-- Check inheritance cases 56 57CREATE TEMP TABLE foochild (fc int) INHERITS (foo); 58 59INSERT INTO foochild VALUES(123,'child',999,-123); 60 61ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99; 62 63SELECT * FROM foo; 64SELECT * FROM foochild; 65 66UPDATE foo SET f4 = f4 + f3 WHERE f4 = 99 RETURNING *; 67 68SELECT * FROM foo; 69SELECT * FROM foochild; 70 71UPDATE foo SET f3 = f3*2 72 FROM int8_tbl i 73 WHERE foo.f1 = i.q2 74 RETURNING *; 75 76SELECT * FROM foo; 77SELECT * FROM foochild; 78 79DELETE FROM foo 80 USING int8_tbl i 81 WHERE foo.f1 = i.q2 82 RETURNING *; 83 84SELECT * FROM foo; 85SELECT * FROM foochild; 86 87DROP TABLE foochild; 88 89-- Rules and views 90 91CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo; 92 93CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD 94 INSERT INTO foo VALUES(new.*, 57); 95 96INSERT INTO voo VALUES(11,'zit'); 97-- fails: 98INSERT INTO voo VALUES(12,'zoo') RETURNING *, f1*2; 99 100-- fails, incompatible list: 101CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD 102 INSERT INTO foo VALUES(new.*, 57) RETURNING *; 103 104CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD 105 INSERT INTO foo VALUES(new.*, 57) RETURNING f1, f2; 106 107-- should still work 108INSERT INTO voo VALUES(13,'zit2'); 109-- works now 110INSERT INTO voo VALUES(14,'zoo2') RETURNING *; 111 112SELECT * FROM foo; 113SELECT * FROM voo; 114 115CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD 116 UPDATE foo SET f1 = new.f1, f2 = new.f2 WHERE f1 = old.f1 117 RETURNING f1, f2; 118 119update voo set f1 = f1 + 1 where f2 = 'zoo2'; 120update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2; 121 122SELECT * FROM foo; 123SELECT * FROM voo; 124 125CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD 126 DELETE FROM foo WHERE f1 = old.f1 127 RETURNING f1, f2; 128 129DELETE FROM foo WHERE f1 = 13; 130DELETE FROM foo WHERE f2 = 'zit' RETURNING *; 131 132SELECT * FROM foo; 133SELECT * FROM voo; 134 135-- Try a join case 136 137CREATE TEMP TABLE joinme (f2j text, other int); 138INSERT INTO joinme VALUES('more', 12345); 139INSERT INTO joinme VALUES('zoo2', 54321); 140INSERT INTO joinme VALUES('other', 0); 141 142CREATE TEMP VIEW joinview AS 143 SELECT foo.*, other FROM foo JOIN joinme ON (f2 = f2j); 144 145SELECT * FROM joinview; 146 147CREATE RULE joinview_u AS ON UPDATE TO joinview DO INSTEAD 148 UPDATE foo SET f1 = new.f1, f3 = new.f3 149 FROM joinme WHERE f2 = f2j AND f2 = old.f2 150 RETURNING foo.*, other; 151 152UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1; 153 154SELECT * FROM joinview; 155SELECT * FROM foo; 156SELECT * FROM voo; 157 158-- Check aliased target relation 159INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok 160INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name 161INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok 162INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok 163