如何在 postgresql 中将 DELETE 的返回插入到 INSERT 中？

Question

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

insert into b (one,two,num) values delete from a where id = 1 returned one, Two, 5;

运行时出现以下错误：

错误：“删除”处或附近的语法错误

任何人都可以指出如何完成此操作，或者有更好的方法吗？或者这是不可能的？

Answer 1

在 PostgreSQL 9.1 之前，您无法执行此操作，因为 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;

Answer 2

在 PostgreSQL 9.1 之前，您可以创建一个像这样的易失性函数（未经测试）：

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

然后只需使用 select move_from_a_to_b(1, 5)。与两个语句相比，函数的优势在于它始终在单个事务中运行 - 无需在客户端代码中显式启动和提交事务。

Answer 3

对于所有版本的 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();

如上面的答案，在 PostgreSQL 9.1 之后，您可以执行以下操作：

WITH tmp AS (DELETE FROM table RETURNING column1, column2, ...)
    INSERT INTO another_table (column1, column2, ...) SELECT * FROM tmp;

Answer 4

您的语法无效。 2 条语句是执行此操作的最佳方法。最直观的方法是先插入，然后删除。

Answer 5

作为“AI W”，两个语句无疑是您的最佳选择，但您也可以考虑为此编写一个触发器。每次在第一个表中删除某些内容时，都会填充另一个表。