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