使用一个cte-postgres进行多个插入

Question

我是postgres的初学者。在做一个模拟项目时，我遇到了这个问题。

我有两个表，比方说t1和t2；与t2有许多关系。

我试图编写一个SQL语句，它首先在t1中插入数据，然后使用t1中的id在t2中插入多行。

像这样的东西。

WITH ins AS (
INSERT INTO t1(t1_col) 
VALUES (4)
RETURNING t1_id
)
INSERT INTO t2(t1_id, t2_col) VALUES (ins.t1_id, 3), (ins.t1_id, 4)...

t1结构->(t1_id primary_key serial，t1_col integer(。

t2结构->(t2_id primary_key serial，t1_id integer，t2_col integer(。

正确的方法是什么。

提前谢谢。

Answer 1

除了使用VALUES子句插入外，您还可以插入SELECT的结果。一般来说，它将是：

WITH ins AS (
INSERT INTO table1(target columns)
VALUES (some values) -- or -- SELECT something FROM somewhere
RETURNING some_id
)
INSERT INTO table2(target columns)
SELECT ins.some_id, other columns or expressions
FROM ins;

多行(固定列表(的变体

WITH ins AS (
INSERT INTO table1(target columns)
VALUES (some values) -- or -- SELECT something FROM somewhere
RETURNING some_id
)
INSERT INTO table2(target columns)
SELECT ins.some_id, UNNEST(ARRAY[3,4,...])
FROM ins;

其中，3,4….是的值列表

Answer 2

这将在一条语句中完成。

WITH ins AS (
INSERT INTO t1(t1_col) 
VALUES (4)
RETURNING t1_id
)
INSERT INTO t2(t1_id, t2_col) 
SELECT ins.t1_id, v.t2_col
from ins
cross join (VALUES (3), (4)) as v(t2_col)
;

如果您从主机语言运行此程序，并且可以将t2值作为数组传递，请对此进行注释，因为这可以简化。

我会把它构建成这样，用于一种主机语言：

with invars as (
select $1 as t1_col, $2::text[] as t2_cols
), ins as (
insert into t1 (t1_col)
select t1_col 
from invars
returning t1_id 
)
insert into t2 (t1_id, t2_col)
select ins.t1_id, unnest(invars.t2_cols) as t2_col
from ins
cross join invars;

然后，从主机，我将把t1_col和一个t2_col值数组作为参数传递给查询。

Answer 3

一个匿名的plpgsql块就可以了。

do language plpgsql
$$
declare
t1id t1.t1_id%type;
begin 
INSERT INTO t1(t1_col) VALUES (4) RETURNING t1_id INTO t1id;
INSERT INTO t2(t1_id, t2_col) 
VALUES (t1id, 3), (t1id, 4)...;
end;
$$;

Answer 4

不需要CTE或变量，可以使用lastval()获取最后生成的标识(或序列(值：

INSERT INTO t1(t1_col) 
VALUES (4);
INSERT INTO t2(t1_id, t2_col) 
VALUES 
(lastval(), 3), 
(lastval(), 4),
...