从选择返回 ID 插入

在 PostgreSql 9.2.4中，我有两个表: user (id, login, password, name)和 dealer (id, user_id)。

我想插入到两个表中，返回创建的经销商的 id。

目前我使用了两个查询:

WITH rows AS (
INSERT INTO "user"
(login, password, name)
VALUES
('dealer1', 'jygbjybk', 'Dealer 1')
RETURNING id
)
INSERT INTO dealer (user_id)
SELECT id
FROM rows;
SELECT currval('dealer_id_seq');

但是我是否可以使用 RETURNING语句通过单个 INSERT查询来实现这一点呢？

sql
postgresql
insert
postgresql-9.2

136000 次浏览

小开

最佳答案

You just need to add a RETURNING id to your INSERT ... SELECT:

WITH rows AS (...)
INSERT INTO dealer (user_id)
SELECT id
FROM rows
RETURNING id;

Demo: http://sqlfiddle.com/#!12/75008/1

小开

For my purposes, I needed it in a variable so I did this:

INSERT INTO dealer (user_id)
SELECT id
FROM rows
RETURNING id INTO l_dealerid;