postgre根据postgres sql函数中的SELECT结果有条件地执行INSERT

Question

如何根据另一个表是否有值对表执行INSERT？这里有一个的例子

create table mygroup (
group_id integer primary key,
other_id integer not null references othertable(other_id)
);
create table myitem (
group_id integer not null references mygroup(group_id),
item_id integer not null
);
create function add_to_group (group_arg integer, item_arg integer, other_arg integer) language sql as $$
select * from mygroup where group_id = group_arg and other_id = other_arg;
-- Do the next statement only if the previous one had a result
insert into myitem (group_id, item_id) values (group_arg, item_arg);
$$;

如果我使用的是plpgsql函数，那么可以使用if (found)来实现它。但是如何使用纯sql函数呢？有没有一种方法可以将两个语句合并为一个语句，比如用JOIN执行INSERT？

Answer 1

我想你只想要exists:

insert into myitem (group_id, item_id) 
select v.group_id, v.item_id
from (values (group_arg, item_arg)) v(group_id, item_id)
where exists (select 1
from mygroup g
where g.group_id = v.group_id and v.other_id = other_arg
);

或者，如果mygroup表中只有一行匹配，则可以使用select：

insert into myitem (group_id, item_id) 
select group_arg, item_arg
from mygroup g
where g.group_id = group_arg and v.other_id = other_arg;

如果重复是可能的，你可以将其调整为：

insert into myitem (group_id, item_id) 
select distinct group_arg, item_arg
from mygroup g
where g.group_id = group_arg and g.other_id = other_arg;