postgresql - 跨三个表的多个插入捕获 postgres 中的序列 ID 列

Question

我有三个表，我把它们缩写成下面的

create table top (top_id serial, top_name text);
create table mid (mid_id serial, top_id integer, mid_name text);
create table bot (bot_id serial, mid_id integer, bot_name text);

我要实现的是

• 在top中插入一个值，并获取它的ID
• 使用相同的 top_id 将多个值插入 mid，
• 对于 mid 中的每个值，使用每个 mid_id 将多个值插入 bot。

所以，最终的结果可能是这样的

top
----
1, first_top
2, second_top

mid
---
1, 1, first_mid_for_first_top
2, 1, second_mid_for_first_top
3, 2, first_mid_for_second_top

bot
---
1, 1, first_bot_for_first_mid
2, 1, second_bot_for_first_mid
3, 3, first_bot_for_third_mid

我的第一个方法是尝试使用一组嵌套的 CTE，但我无法将其变成有效的语法(经过考虑，我认为这不是正确的方法)。

我的第二种方法是尝试将值存储到某种变量中，但我不知道如何在 pg 命令行工具之外执行此操作。

我所追求的是类似于

top_id_one = insert into top(null, 'first_top') returning top_id
mid_id_one = insert into mid(null, top_id_one, 'first_mid_for_first_top) returning mid_id
insert into bot(null, mid_id_one, 'first_bot_for_first_mid')

例如，在我可以粘贴到 PG admin 中的“纯 sql”中。

我意识到上面的示例中存在一些问题(例如使用“top”作为表名)。我的探索避免了这些问题，但我试图找到使我的示例有意义的表名。

Answer 1

您可以通过对插入到 mid 中的行编号来选择使用哪个 mid_id 将值插入到 bot 中。使用这些行号 (rn) 来识别插入到 bot 中的值:

with top_ins as (
    insert into top (top_name)
    values ('first_top')
    returning top_id
    ),
mid_ins as (
    insert into mid (top_id, mid_name)
    select top_id, name
    from (
        values 
            ('first_mid'), 
            ('second_mid')
        ) v(name)
    cross join top_ins
    returning mid_id
    ),
mid_ins_rn as (
    select mid_id, row_number() over() rn
    from mid_ins
    order by 1
    )
insert into bot (mid_id, bot_name)
select mid_id, name
from (
    values 
        (1, 'first_bot_for_first_mid'), 
        (1, 'second_bot_for_first_mid'),
        (2, 'first_bot_for_second_mid')
    ) v(rn, name)
join mid_ins_rn
using (rn);

结果:

select * from top;

 top_id | top_name  
--------+-----------
      1 | first_top
(1 row)

select * from mid;

 mid_id | top_id |  mid_name  
--------+--------+------------
      1 |      1 | first_mid
      2 |      1 | second_mid
(2 rows)

select * from bot;

 bot_id | mid_id |         bot_name         
--------+--------+--------------------------
      1 |      1 | first_bot_for_first_mid
      2 |      1 | second_bot_for_first_mid
      3 |      2 | first_bot_for_second_mid
(3 rows)