PostgreSQL中如何在插入主表记录前确保关联表数据存在？——未知动物ID时的组合插入方案问询

我来帮你搞定这个场景！你的需求很明确：要在插入动物配对记录前，自动确保配对的两个动物已经存在于主表中，而且得用纯SQL函数实现原子操作。

先聊聊你之前写的函数为啥会出问题：

• 当两个动物都是新记录时，union会把new_records和animals里的结果合并，导致插入两条完全相同的组合记录；
• 当一个存在一个不存在时，虽然理论上第二个select能拿到正确ID，但逻辑绕了弯路，很容易出现预期外的情况。

正确的纯SQL函数实现 #

其实我们可以简化逻辑，用更直接的方式完成这个需求：

create or replace function add_combi(p_male text, p_female text) returns void language sql as $$
with ensure_animals as (
    -- 确保两个动物都存在，不存在则插入，存在则跳过
    insert into animals (name) values (p_male), (p_female) 
    on conflict (name) do nothing
)
-- 直接从animals表获取ID插入组合记录
insert into combinations (male_animal_id, female_animal_id)
select 
    (select id from animals where name = p_male),
    (select id from animals where name = p_female);
$$;

逻辑解释 #

这个函数的核心思路就是先保证依赖数据存在，再插入关联记录：

1. ensure_animals子句：尝试插入传入的两个动物名称，通过ON CONFLICT (name) DO NOTHING避免重复插入已存在的记录。这一步执行完后，不管这两个动物原本是否存在，animals表中肯定都能找到它们的记录。
2. 插入组合记录：直接从animals表中查询两个动物的ID，插入到combinations表中。

验证各种场景 #

这个函数能完美覆盖你需要的所有情况：

• 两个动物都不存在：自动插入两条动物记录，然后插入一条组合记录；
• 一个存在一个不存在：插入不存在的那条动物记录，然后插入组合记录；
• 两个都已存在：跳过动物插入操作，直接插入组合记录；
• 不会出现重复插入组合的问题，因为只执行一次insert into combinations。

内容的提问来源于stack exchange，提问作者luukvhoudt