src/test/regress/expected/returning.out

   1 --
   2 -- Test INSERT/UPDATE/DELETE RETURNING
   3 --
   4 -- Simple cases
   5 CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
   6 INSERT 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
  16 SELECT * FROM foo;
  17  f1 |  f2  | f3
  18 ----+------+----
  19   1 | test | 42
  20   2 | More | 11
  21   3 | MORE | 16
  22 (3 rows)
  23
  24 UPDATE 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
  32 SELECT * FROM foo;
  33  f1 |  f2  | f3
  34 ----+------+----
  35   1 | test | 42
  36   2 | more | 42
  37   3 | more | 42
  38 (3 rows)
  39
  40 DELETE 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
  46 SELECT * 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
  54 INSERT 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
  63 UPDATE 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
  73 DELETE 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
  84 UPDATE 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
  93 SELECT * FROM foo;
  94  f1 |  f2  | f3
  95 ----+------+----
  96   2 | more | 42
  97   1 | test | 84
  98 (2 rows)
  99
 100 DELETE 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
 109 SELECT * FROM foo;
 110  f1 |  f2  | f3
 111 ----+------+----
 112   2 | more | 42
 113 (1 row)
 114
 115 -- Check inheritance cases
 116 CREATE TEMP TABLE foochild (fc int) INHERITS (foo);
 117 INSERT INTO foochild VALUES(123,'child',999,-123);
 118 ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99;
 119 SELECT * FROM foo;
 120  f1  |  f2   | f3  | f4
 121 -----+-------+-----+----
 122    2 | more  |  42 | 99
 123  123 | child | 999 | 99
 124 (2 rows)
 125
 126 SELECT * FROM foochild;
 127  f1  |  f2   | f3  |  fc  | f4
 128 -----+-------+-----+------+----
 129  123 | child | 999 | -123 | 99
 130 (1 row)
 131
 132 UPDATE 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
 139 SELECT * FROM foo;
 140  f1  |  f2   | f3  |  f4
 141 -----+-------+-----+------
 142    2 | more  |  42 |  141
 143  123 | child | 999 | 1098
 144 (2 rows)
 145
 146 SELECT * FROM foochild;
 147  f1  |  f2   | f3  |  fc  |  f4
 148 -----+-------+-----+------+------
 149  123 | child | 999 | -123 | 1098
 150 (1 row)
 151
 152 UPDATE 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
 161 SELECT * FROM foo;
 162  f1  |  f2   |  f3  |  f4
 163 -----+-------+------+------
 164    2 | more  |   42 |  141
 165  123 | child | 1998 | 1098
 166 (2 rows)
 167
 168 SELECT * FROM foochild;
 169  f1  |  f2   |  f3  |  fc  |  f4
 170 -----+-------+------+------+------
 171  123 | child | 1998 | -123 | 1098
 172 (1 row)
 173
 174 DELETE 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
 183 SELECT * FROM foo;
 184  f1 |  f2  | f3 | f4
 185 ----+------+----+-----
 186   2 | more | 42 | 141
 187 (1 row)
 188
 189 SELECT * FROM foochild;
 190  f1 | f2 | f3 | fc | f4
 191 ----+----+----+----+----
 192 (0 rows)
 193
 194 DROP TABLE foochild;
 195 -- Rules and views
 196 CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo;
 197 CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD
 198   INSERT INTO foo VALUES(new.*, 57);
 199 INSERT INTO voo VALUES(11,'zit');
 200 -- fails:
 201 INSERT INTO voo VALUES(12,'zoo') RETURNING *, f1*2;
 202 ERROR:  cannot perform INSERT RETURNING on relation "voo"
 203 HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
 204 -- fails, incompatible list:
 205 CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
 206   INSERT INTO foo VALUES(new.*, 57) RETURNING *;
 207 ERROR:  RETURNING list has too many entries
 208 CREATE 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
 211 INSERT INTO voo VALUES(13,'zit2');
 212 -- works now
 213 INSERT INTO voo VALUES(14,'zoo2') RETURNING *;
 214  f1 |  f2
 215 ----+------
 216  14 | zoo2
 217 (1 row)
 218
 219 SELECT * 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
 228 SELECT * FROM voo;
 229  f1 |  f2
 230 ----+------
 231   2 | more
 232  11 | zit
 233  13 | zit2
 234  14 | zoo2
 235 (4 rows)
 236
 237 CREATE 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;
 240 update voo set f1 = f1 + 1 where f2 = 'zoo2';
 241 update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
 242  f1 |  f2  | ?column?
 243 ----+------+----------
 244  16 | zoo2 |       32
 245 (1 row)
 246
 247 SELECT * 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
 256 SELECT * FROM voo;
 257  f1 |  f2
 258 ----+------
 259   2 | more
 260  11 | zit
 261  13 | zit2
 262  16 | zoo2
 263 (4 rows)
 264
 265 CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
 266   DELETE FROM foo WHERE f1 = old.f1
 267   RETURNING f1, f2;
 268 DELETE FROM foo WHERE f1 = 13;
 269 DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
 270  f1 | f2  | f3 | f4
 271 ----+-----+----+----
 272  11 | zit | 57 | 99
 273 (1 row)
 274
 275 SELECT * FROM foo;
 276  f1 |  f2  | f3 | f4
 277 ----+------+----+-----
 278   2 | more | 42 | 141
 279  16 | zoo2 | 57 |  99
 280 (2 rows)
 281
 282 SELECT * FROM voo;
 283  f1 |  f2
 284 ----+------
 285   2 | more
 286  16 | zoo2
 287 (2 rows)
 288
 289 -- Try a join case
 290 CREATE TEMP TABLE joinme (f2j text, other int);
 291 INSERT INTO joinme VALUES('more', 12345);
 292 INSERT INTO joinme VALUES('zoo2', 54321);
 293 INSERT INTO joinme VALUES('other', 0);
 294 CREATE TEMP VIEW joinview AS
 295   SELECT foo.*, other FROM foo JOIN joinme ON (f2 = f2j);
 296 SELECT * 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
 303 CREATE 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;
 307 UPDATE 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
 313 SELECT * 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
 320 SELECT * FROM foo;
 321  f1 |  f2  | f3 | f4
 322 ----+------+----+-----
 323   2 | more | 42 | 141
 324  17 | zoo2 | 57 |  99
 325 (2 rows)
 326
 327 SELECT * FROM voo;
 328  f1 |  f2
 329 ----+------
 330   2 | more
 331  17 | zoo2
 332 (2 rows)
 333
 334 -- Check aliased target relation
 335 INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok
 336  f1 | f2 | f3 | f4
 337 ----+----+----+----
 338   4 |    | 42 | 99
 339 (1 row)
 340
 341 INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name
 342 ERROR:  invalid reference to FROM-clause entry for table "foo"
 343 LINE 1: INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*;
 344                                                         ^
 345 HINT:  Perhaps you meant to reference the table alias "bar".
 346 INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok
 347  f1 | f2 | f3 | f4
 348 ----+----+----+----
 349   5 |    | 42 | 99
 350 (1 row)
 351
 352 INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok
 353  f3
 354 ----
 355  42
 356 (1 row)
 357