1-- 2-- Test INSERT/UPDATE/DELETE RETURNING 3-- 4-- Simple cases 5CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42); 6INSERT INTO foo (f2,f3) 7 VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9) 8 RETURNING *, f1+f3 AS sum; 9 f1 | f2 | f3 | sum 10----+------+----+----- 11 1 | test | 42 | 43 12 2 | More | 11 | 13 13 3 | MORE | 16 | 19 14(3 rows) 15 16SELECT * FROM foo; 17 f1 | f2 | f3 18----+------+---- 19 1 | test | 42 20 2 | More | 11 21 3 | MORE | 16 22(3 rows) 23 24UPDATE foo SET f2 = lower(f2), f3 = DEFAULT RETURNING foo.*, f1+f3 AS sum13; 25 f1 | f2 | f3 | sum13 26----+------+----+------- 27 1 | test | 42 | 43 28 2 | more | 42 | 44 29 3 | more | 42 | 45 30(3 rows) 31 32SELECT * FROM foo; 33 f1 | f2 | f3 34----+------+---- 35 1 | test | 42 36 2 | more | 42 37 3 | more | 42 38(3 rows) 39 40DELETE FROM foo WHERE f1 > 2 RETURNING f3, f2, f1, least(f1,f3); 41 f3 | f2 | f1 | least 42----+------+----+------- 43 42 | more | 3 | 3 44(1 row) 45 46SELECT * FROM foo; 47 f1 | f2 | f3 48----+------+---- 49 1 | test | 42 50 2 | more | 42 51(2 rows) 52 53-- Subplans and initplans in the RETURNING list 54INSERT INTO foo SELECT f1+10, f2, f3+99 FROM foo 55 RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan, 56 EXISTS(SELECT * FROM int4_tbl) AS initplan; 57 f1 | f2 | f3 | subplan | initplan 58----+------+-----+---------+---------- 59 11 | test | 141 | t | t 60 12 | more | 141 | f | t 61(2 rows) 62 63UPDATE foo SET f3 = f3 * 2 64 WHERE f1 > 10 65 RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan, 66 EXISTS(SELECT * FROM int4_tbl) AS initplan; 67 f1 | f2 | f3 | subplan | initplan 68----+------+-----+---------+---------- 69 11 | test | 282 | t | t 70 12 | more | 282 | f | t 71(2 rows) 72 73DELETE FROM foo 74 WHERE f1 > 10 75 RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan, 76 EXISTS(SELECT * FROM int4_tbl) AS initplan; 77 f1 | f2 | f3 | subplan | initplan 78----+------+-----+---------+---------- 79 11 | test | 282 | t | t 80 12 | more | 282 | f | t 81(2 rows) 82 83-- Joins 84UPDATE foo SET f3 = f3*2 85 FROM int4_tbl i 86 WHERE foo.f1 + 123455 = i.f1 87 RETURNING foo.*, i.f1 as "i.f1"; 88 f1 | f2 | f3 | i.f1 89----+------+----+-------- 90 1 | test | 84 | 123456 91(1 row) 92 93SELECT * FROM foo; 94 f1 | f2 | f3 95----+------+---- 96 2 | more | 42 97 1 | test | 84 98(2 rows) 99 100DELETE FROM foo 101 USING int4_tbl i 102 WHERE foo.f1 + 123455 = i.f1 103 RETURNING foo.*, i.f1 as "i.f1"; 104 f1 | f2 | f3 | i.f1 105----+------+----+-------- 106 1 | test | 84 | 123456 107(1 row) 108 109SELECT * FROM foo; 110 f1 | f2 | f3 111----+------+---- 112 2 | more | 42 113(1 row) 114 115-- Check inheritance cases 116CREATE TEMP TABLE foochild (fc int) INHERITS (foo); 117INSERT INTO foochild VALUES(123,'child',999,-123); 118ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99; 119SELECT * FROM foo; 120 f1 | f2 | f3 | f4 121-----+-------+-----+---- 122 2 | more | 42 | 99 123 123 | child | 999 | 99 124(2 rows) 125 126SELECT * FROM foochild; 127 f1 | f2 | f3 | fc | f4 128-----+-------+-----+------+---- 129 123 | child | 999 | -123 | 99 130(1 row) 131 132UPDATE foo SET f4 = f4 + f3 WHERE f4 = 99 RETURNING *; 133 f1 | f2 | f3 | f4 134-----+-------+-----+------ 135 2 | more | 42 | 141 136 123 | child | 999 | 1098 137(2 rows) 138 139SELECT * FROM foo; 140 f1 | f2 | f3 | f4 141-----+-------+-----+------ 142 2 | more | 42 | 141 143 123 | child | 999 | 1098 144(2 rows) 145 146SELECT * FROM foochild; 147 f1 | f2 | f3 | fc | f4 148-----+-------+-----+------+------ 149 123 | child | 999 | -123 | 1098 150(1 row) 151 152UPDATE foo SET f3 = f3*2 153 FROM int8_tbl i 154 WHERE foo.f1 = i.q2 155 RETURNING *; 156 f1 | f2 | f3 | f4 | q1 | q2 157-----+-------+------+------+------------------+----- 158 123 | child | 1998 | 1098 | 4567890123456789 | 123 159(1 row) 160 161SELECT * FROM foo; 162 f1 | f2 | f3 | f4 163-----+-------+------+------ 164 2 | more | 42 | 141 165 123 | child | 1998 | 1098 166(2 rows) 167 168SELECT * FROM foochild; 169 f1 | f2 | f3 | fc | f4 170-----+-------+------+------+------ 171 123 | child | 1998 | -123 | 1098 172(1 row) 173 174DELETE FROM foo 175 USING int8_tbl i 176 WHERE foo.f1 = i.q2 177 RETURNING *; 178 f1 | f2 | f3 | f4 | q1 | q2 179-----+-------+------+------+------------------+----- 180 123 | child | 1998 | 1098 | 4567890123456789 | 123 181(1 row) 182 183SELECT * FROM foo; 184 f1 | f2 | f3 | f4 185----+------+----+----- 186 2 | more | 42 | 141 187(1 row) 188 189SELECT * FROM foochild; 190 f1 | f2 | f3 | fc | f4 191----+----+----+----+---- 192(0 rows) 193 194DROP TABLE foochild; 195-- Rules and views 196CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo; 197CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD 198 INSERT INTO foo VALUES(new.*, 57); 199INSERT INTO voo VALUES(11,'zit'); 200-- fails: 201INSERT INTO voo VALUES(12,'zoo') RETURNING *, f1*2; 202ERROR: cannot perform INSERT RETURNING on relation "voo" 203HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause. 204-- fails, incompatible list: 205CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD 206 INSERT INTO foo VALUES(new.*, 57) RETURNING *; 207ERROR: RETURNING list has too many entries 208CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD 209 INSERT INTO foo VALUES(new.*, 57) RETURNING f1, f2; 210-- should still work 211INSERT INTO voo VALUES(13,'zit2'); 212-- works now 213INSERT INTO voo VALUES(14,'zoo2') RETURNING *; 214 f1 | f2 215----+------ 216 14 | zoo2 217(1 row) 218 219SELECT * FROM foo; 220 f1 | f2 | f3 | f4 221----+------+----+----- 222 2 | more | 42 | 141 223 11 | zit | 57 | 99 224 13 | zit2 | 57 | 99 225 14 | zoo2 | 57 | 99 226(4 rows) 227 228SELECT * FROM voo; 229 f1 | f2 230----+------ 231 2 | more 232 11 | zit 233 13 | zit2 234 14 | zoo2 235(4 rows) 236 237CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD 238 UPDATE foo SET f1 = new.f1, f2 = new.f2 WHERE f1 = old.f1 239 RETURNING f1, f2; 240update voo set f1 = f1 + 1 where f2 = 'zoo2'; 241update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2; 242 f1 | f2 | ?column? 243----+------+---------- 244 16 | zoo2 | 32 245(1 row) 246 247SELECT * FROM foo; 248 f1 | f2 | f3 | f4 249----+------+----+----- 250 2 | more | 42 | 141 251 11 | zit | 57 | 99 252 13 | zit2 | 57 | 99 253 16 | zoo2 | 57 | 99 254(4 rows) 255 256SELECT * FROM voo; 257 f1 | f2 258----+------ 259 2 | more 260 11 | zit 261 13 | zit2 262 16 | zoo2 263(4 rows) 264 265CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD 266 DELETE FROM foo WHERE f1 = old.f1 267 RETURNING f1, f2; 268DELETE FROM foo WHERE f1 = 13; 269DELETE FROM foo WHERE f2 = 'zit' RETURNING *; 270 f1 | f2 | f3 | f4 271----+-----+----+---- 272 11 | zit | 57 | 99 273(1 row) 274 275SELECT * FROM foo; 276 f1 | f2 | f3 | f4 277----+------+----+----- 278 2 | more | 42 | 141 279 16 | zoo2 | 57 | 99 280(2 rows) 281 282SELECT * FROM voo; 283 f1 | f2 284----+------ 285 2 | more 286 16 | zoo2 287(2 rows) 288 289-- Try a join case 290CREATE TEMP TABLE joinme (f2j text, other int); 291INSERT INTO joinme VALUES('more', 12345); 292INSERT INTO joinme VALUES('zoo2', 54321); 293INSERT INTO joinme VALUES('other', 0); 294CREATE TEMP VIEW joinview AS 295 SELECT foo.*, other FROM foo JOIN joinme ON (f2 = f2j); 296SELECT * FROM joinview; 297 f1 | f2 | f3 | f4 | other 298----+------+----+-----+------- 299 2 | more | 42 | 141 | 12345 300 16 | zoo2 | 57 | 99 | 54321 301(2 rows) 302 303CREATE RULE joinview_u AS ON UPDATE TO joinview DO INSTEAD 304 UPDATE foo SET f1 = new.f1, f3 = new.f3 305 FROM joinme WHERE f2 = f2j AND f2 = old.f2 306 RETURNING foo.*, other; 307UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1; 308 f1 | f2 | f3 | f4 | other | ?column? 309----+------+----+----+-------+---------- 310 17 | zoo2 | 57 | 99 | 54321 | 54322 311(1 row) 312 313SELECT * FROM joinview; 314 f1 | f2 | f3 | f4 | other 315----+------+----+-----+------- 316 2 | more | 42 | 141 | 12345 317 17 | zoo2 | 57 | 99 | 54321 318(2 rows) 319 320SELECT * FROM foo; 321 f1 | f2 | f3 | f4 322----+------+----+----- 323 2 | more | 42 | 141 324 17 | zoo2 | 57 | 99 325(2 rows) 326 327SELECT * FROM voo; 328 f1 | f2 329----+------ 330 2 | more 331 17 | zoo2 332(2 rows) 333 334-- Check aliased target relation 335INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok 336 f1 | f2 | f3 | f4 337----+----+----+---- 338 4 | | 42 | 99 339(1 row) 340 341INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name 342ERROR: invalid reference to FROM-clause entry for table "foo" 343LINE 1: INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; 344 ^ 345HINT: Perhaps you meant to reference the table alias "bar". 346INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok 347 f1 | f2 | f3 | f4 348----+----+----+---- 349 5 | | 42 | 99 350(1 row) 351 352INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok 353 f3 354---- 355 42 356(1 row) 357 358