如何将DELETE的返回值插入到PostgreSQL的INSERT中？ [英] How can I insert the return of DELETE into INSERT in postgresql?

问题描述

我正在尝试从一个表中删除一行，并将其与一些其他数据一起插入到另一个表中。我知道这可以通过两个单独的命令来完成，一个要删除，另一个要插入新表。但是，我正在尝试将它们组合在一起，但它不起作用，到目前为止，这是我的查询：

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:

插入b（一，二，num ）值从id = 1的地方删除，返回一，二，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?

推荐答案

在PostgreSQL 9.1之前，您可以创建一个易失函数，例如（unested） em>：

Before PostgreSQL 9.1 you can create a volatile function like this (untested):

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;
$$

然后使用选择move_from_a_to_b（1， 5）。函数具有两条语句相比总是在单个事务中运行的优点-无需在客户端代码中显式启动和提交事务。

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.

这篇关于如何将DELETE的返回值插入到PostgreSQL的INSERT中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！