sql - 如何从 INSERT 返回被插入行以外的值

Question

我有大量行要同时插入到 PostgreSQL 数据库中。我需要跟踪为插入的每一行分配的 id。例如假设我们有表:

CREATE TABLE example
(
  id serial,
  name text,
  CONSTRAINT example_pkey PRIMARY KEY (id),
);

现在我有一些不想插入 ID 的数据(因为序列 ID 列将分配一个新 ID)，但我需要跟踪旧 ID 和新 ID 之间的映射:

old id | name
-------------
-1     | foo
-2     | bar
-3     | baz

所以我写了这个查询

WITH data(oldid,name) AS ( VALUES
    (-1,'foo'),
    (-2,'bar'),
    (-3,'baz')
)
INSERT INTO example(name)
SELECT name FROM data d
RETURNING id, d.oldid

期待得到像这样的东西:

id  | oldid
-----------
1   | -1
2   | -2
3   | -3

但是这不起作用，因为我不相信您可以返回未插入的列。有没有其他方法可以做到这一点？

Answer 1

我最终创建了一个函数来包装单行的插入:

CREATE OR REPLACE FUNCTION add_example(
    in_name text)
  RETURNS integer AS
$BODY$
DECLARE
 new_id integer;
BEGIN
    INSERT INTO example(name)
    VALUES (in_name) RETURNING id INTO new_id;
    RETURN new_id;
END;
$BODY$
LANGUAGE plpgsql;

然后我可以做:

WITH data(oldid, name) AS (VALUES
    (-1,'foo'),
    (-2,'bar'),
    (-3,'baz')
)
SELECT oldid, add_example(name) AS id
FROM data

返回我所期望的。不过，我想看看是否可以在没有该功能的情况下完成此操作。