2
-- Test INSERT/UPDATE/DELETE RETURNING
7
CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
9
INSERT INTO foo (f2,f3)
10
VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9)
11
RETURNING *, f1+f3 AS sum;
15
UPDATE foo SET f2 = lower(f2), f3 = DEFAULT RETURNING foo.*, f1+f3 AS sum13;
19
DELETE FROM foo WHERE f1 > 2 RETURNING f3, f2, f1, least(f1,f3);
23
-- Subplans and initplans in the RETURNING list
25
INSERT 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;
29
UPDATE foo SET f3 = f3 * 2
31
RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
32
EXISTS(SELECT * FROM int4_tbl) AS initplan;
36
RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
37
EXISTS(SELECT * FROM int4_tbl) AS initplan;
41
UPDATE foo SET f3 = f3*2
43
WHERE foo.f1 + 123455 = i.f1
44
RETURNING foo.*, i.f1 as "i.f1";
50
WHERE foo.f1 + 123455 = i.f1
51
RETURNING foo.*, i.f1 as "i.f1";
55
-- Check inheritance cases
57
CREATE TEMP TABLE foochild (fc int) INHERITS (foo);
59
INSERT INTO foochild VALUES(123,'child',999,-123);
61
ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99;
64
SELECT * FROM foochild;
66
UPDATE foo SET f4 = f4 + f3 WHERE f4 = 99 RETURNING *;
69
SELECT * FROM foochild;
71
UPDATE foo SET f3 = f3*2
77
SELECT * FROM foochild;
85
SELECT * FROM foochild;
91
CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo;
93
CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD
94
INSERT INTO foo VALUES(new.*, 57);
96
INSERT INTO voo VALUES(11,'zit');
98
INSERT INTO voo VALUES(12,'zoo') RETURNING *, f1*2;
100
-- fails, incompatible list:
101
CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
102
INSERT INTO foo VALUES(new.*, 57) RETURNING *;
104
CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
105
INSERT INTO foo VALUES(new.*, 57) RETURNING f1, f2;
108
INSERT INTO voo VALUES(13,'zit2');
110
INSERT INTO voo VALUES(14,'zoo2') RETURNING *;
115
CREATE 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
119
update voo set f1 = f1 + 1 where f2 = 'zoo2';
120
update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
125
CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
126
DELETE FROM foo WHERE f1 = old.f1
129
DELETE FROM foo WHERE f1 = 13;
130
DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
137
CREATE TEMP TABLE joinme (f2j text, other int);
138
INSERT INTO joinme VALUES('more', 12345);
139
INSERT INTO joinme VALUES('zoo2', 54321);
140
INSERT INTO joinme VALUES('other', 0);
142
CREATE TEMP VIEW joinview AS
143
SELECT foo.*, other FROM foo JOIN joinme ON (f2 = f2j);
145
SELECT * FROM joinview;
147
CREATE 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;
152
UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1;
154
SELECT * FROM joinview;