postgresql 我可以在另一个 INSERT 中使用 INSERT...RETURNING 的返回值吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6560447/
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
Can I use return value of INSERT...RETURNING in another INSERT?
提问by Eike Cochu
Is something like this possible?
这样的事情可能吗？
INSERT INTO Table2 (val)
VALUES ((INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id));
like using the return value as value to insert a row in a second table with a reference to the first table?
像使用返回值作为值在第二个表中插入一行并引用第一个表？
回答by Denis de Bernardy
You can do so starting with Postgres 9.1:
你可以从 Postgres 9.1 开始这样做：
with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows
In the meanwhile, if you're only interested in the id, you can do so with a trigger:
同时，如果您只对 id 感兴趣，则可以使用触发器来实现：
create function t1_ins_into_t2()
returns trigger
as $$
begin
insert into table2 (val) values (new.id);
return new;
end;
$$ language plpgsql;
create trigger t1_ins_into_t2
after insert on table1
for each row
execute procedure t1_ins_into_t2();
回答by Alexandre Assi
The best practice for this situation. Use RETURNING … INTO.
这种情况的最佳实践。使用RETURNING … INTO.
INSERT INTO teams VALUES (...) RETURNING id INTO last_id;
回答by Bhindi
In line with the answer given by Denis de Bernardy..
与丹尼斯·德·伯纳迪 (Denis de Bernardy) 给出的答案一致..
If you want id to be returned afterwards as well and want to insert more things into Table2:
如果您还想在之后返回 id 并想在 Table2 中插入更多内容：
with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val, val2, val3)
SELECT id, 'val2value', 'val3value'
FROM rows
RETURNING val
回答by mu is too short
You can use the lastval()function:
您可以使用该lastval()功能：
Return value most recently obtained with
nextvalfor any sequence
返回最近获得
nextval的任何序列的值
So something like this:
所以像这样：
INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val) VALUES (lastval());
This will work fine as long as no one calls nextval()on any other sequence (in the current session) between your INSERTs.
只要nextval()在您的 INSERT 之间没有人调用任何其他序列（在当前会话中），这将正常工作。
As Denisnoted below and I warned about above, using lastval()can get you into trouble if another sequence is accessed using nextval()between your INSERTs. This could happen if there was an INSERT trigger on Table1that manually called nextval()on a sequence or, more likely, did an INSERT on a table with a SERIALor BIGSERIALprimary key. If you want to be really paranoid (a good thing, theyreally are you to get you after all), then you could use currval()but you'd need to know the name of the relevant sequence:
正如丹尼斯在下面提到的和我在上面警告过的，lastval()如果nextval()在你的 INSERT 之间使用另一个序列访问，使用会给你带来麻烦。如果在序列上Table1手动调用的 INSERT 触发器上有一个 INSERT 触发器，nextval()或者更有可能在具有主键SERIAL或BIGSERIAL主键的表上执行 INSERT，则可能会发生这种情况。如果你真的想变得偏执（一件好事，毕竟他们真的是让你得到你），那么你可以使用，currval()但你需要知道相关序列的名称：
INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val) VALUES (currval('Table1_id_seq'::regclass));
The automatically generated sequence is usually named t_c_seqwhere tis the table name and cis the column name but you can always find out by going into psqland saying:
自动生成的序列通常命名为t_c_seqwhere tis the table name and cis the column name 但是你总是可以通过进入psql并说：
=> \d table_name;
and then looking at the default value for the column in question, for example:
然后查看相关列的默认值，例如：
id | integer | not null default nextval('people_id_seq'::regclass)
FYI: lastval()is, more or less, the PostgreSQL version of MySQL's LAST_INSERT_ID. I only mention this because a lot of people are more familiar with MySQL than PostgreSQL so linking lastval()to something familiar might clarify things.
仅供参考：lastval()或多或少是 MySQL 的LAST_INSERT_ID. 我之所以提到这一点，是因为很多人比 PostgreSQL 更熟悉 MySQL，因此链接lastval()到熟悉的东西可能会澄清一些事情。
回答by Anders B
DO $$
DECLARE tableId integer;
BEGIN
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id INTO tableId;
INSERT INTO Table2 (val) VALUES (tableId);
END $$;
Tested with psql (10.3, server 9.6.8)
用 psql（10.3，服务器 9.6.8）测试
回答by kemado77
table_ex
id default nextval('table_id_seq'::regclass),
camp1 varchar
camp2 varchar
表_ex
id 默认 nextval('table_id_seq'::regclass),
营1 varchar
营地2 varchar
INSERT INTO table_ex(camp1,camp2) VALUES ('xxx','123') RETURNING id
