UNION接受SELECT语句,但不接受INSERT语句。
假设创建了一个非唯一id的表:
CREATE TABLE some_table (id int, text_field text);
INSERT INTO some_table VALUES (1, 'a'),(1, 'b');
所以初始请求可以看起来像这样:
WITH delete_rows AS (DELETE FROM some_table WHERE id = 1 RETURNING *),
insert_rows AS (
INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *
)
(SELECT 'deleted', * FROM delete_rows)
UNION ALL
(SELECT 'inserted', * FROM insert_rows);
?column? | id | text_field
deleted | 1 | a
deleted | 1 | b
inserted | 1 | c
inserted | 1 | d
inserted | 1 | e
(5 rows)
如果不需要删除的行:
WITH delete_rows AS (DELETE FROM some_table WHERE id = 1),
insert_rows AS (
INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *
)
SELECT * FROM insert_rows;
或者只是:
WITH delete_rows AS (DELETE FROM some_table WHERE id = 1)
INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *;
id | text_field
1 | c
1 | d
1 | e
(3 rows)