如何在 postgresql 中将 DELETE 的返回插入到 INSERT 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6577823/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I insert the return of DELETE into INSERT in postgresql?
提问by lanrat
I am trying to delete a row from one table and insert it with some additional data into another. I know this can be done in two separate commands, one to delete and another to insert into the new table. However I am trying to combine them and it is not working, this is my query so far:
我正在尝试从一个表中删除一行并将其与一些其他数据一起插入到另一个表中。我知道这可以通过两个单独的命令来完成，一个是删除，另一个是插入到新表中。但是我正在尝试将它们组合起来，但它不起作用，这是我目前的查询：
insert into b (one,two,num) values delete from a where id = 1 returning one, two, 5;
insert into b (one,two,num) values delete from a where id = 1 returning one, two, 5;
When running that I get the following error:
运行时，我收到以下错误：
ERROR: syntax error at or near "delete"
错误：“删除”处或附近的语法错误
Can anyone point out how to accomplish this, or is there a better way? or is it not possible?
谁能指出如何实现这一点，或者有更好的方法吗？还是不可能？
采纳答案by Tometzky
Before PostgreSQL 9.1 you can create a volatile function like this (untested):
在 PostgreSQL 9.1 之前，您可以创建一个像这样的 volatile 函数（未经测试）：
create function move_from_a_to_b(_id integer, _num integer)
returns void language plpgsql volatile as
$$
declare
_one integer;
_two integer;
begin
delete from a where id = _id returning one, two into strict _one, _two;
insert into b (one,two,num) values (_one, _two, _num);
end;
$$
And then just use select move_from_a_to_b(1, 5). A function has the advantage over two statements that it will always run in single transaction — there's no need to explicitly start and commit transaction in client code.
然后只需使用select move_from_a_to_b(1, 5). 与两条语句相比，函数的优势在于它始终在单个事务中运行——无需在客户端代码中显式启动和提交事务。
回答by Peter Eisentraut
You cannot do this before PostgreSQL 9.1, which is not yet released. And then the syntax would be
您不能在尚未发布的 PostgreSQL 9.1 之前执行此操作。然后语法是
WITH foo AS (DELETE FROM a WHERE id = 1 RETURNING one, two, 5)
INSERT INTO b (one, two, num) SELECT * FROM foo;
回答by Khalil
For all version of PostgreSQL, you can create a trigger function for deleting rows from a table and inserting them to another table. But it seems slower than bulk insert that is released in PostgreSQL 9.1. You just need to move the old data into the another table before it gets deleted. This is done with the OLD data type:
对于所有版本的 PostgreSQL，您都可以创建一个触发器函数，用于从表中删除行并将它们插入到另一个表中。但它似乎比 PostgreSQL 9.1 中发布的批量插入慢。您只需要在旧数据被删除之前将其移动到另一个表中。这是通过 OLD 数据类型完成的：
CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
BEGIN
INSERT INTO another_table VALUES(OLD.column1, OLD.column2,...);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER moveDeleted
BEFORE DELETE ON table
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();
As above answer, after PostgreSQL 9.1 you can do this:
如上答案，在 PostgreSQL 9.1 之后，您可以执行以下操作：
WITH tmp AS (DELETE FROM table RETURNING column1, column2, ...)
INSERT INTO another_table (column1, column2, ...) SELECT * FROM tmp;
回答by Al W
That syntax you have there isn't valid. 2 statements is the best way to do this. The most intuitive way to do it would be to do the insert first and the delete second.
您在那里使用的语法无效。2 语句是执行此操作的最佳方法。最直观的方法是先插入，然后再删除。
回答by plang
As "AI W", two statements are certainly the best option for you, but you could also consider writing a trigger for that. Each time something is deleted in your first table, another is filled.
作为“AI W”，两个语句当然是您的最佳选择，但您也可以考虑为此编写触发器。每次删除第一个表中的内容时，都会填充另一个。
