PostgreSQL 10中PL/pgSQL变量无法在IN右侧使用的解决方法

咱们先搞懂你遇到的报错原因：PostgreSQL里IN运算符的语法规则很明确，它后面只能接括号包裹的逗号分隔值列表或者返回单列的子查询，直接跟数组变量是不被识别的，所以你写if block.schedule_id in block.schedule_ids才会触发42601语法错误。

接下来给你两种可行的解决方法，帮你实现子查询结果复用的需求：

方法一：用= ANY()替代IN（最推荐） #

ANY()运算符天生支持直接接收数组参数，语法简洁性能也更好。注意赋值数组的时候，要用array_agg()或者array()构造正确的整数数组，避免直接赋值单行单列导致的类型不匹配：

create function "is_valid_slot_task"() returns trigger as $$ <<block>> 
declare 
  schedule_id integer; 
  schedule_ids integer[]; 
begin 
  -- 获取当前slot对应的schedule_id
  block.schedule_id := (select c."schedule_id" 
                        from "slot" as s 
                        join "column" as c on c."column_id" = s."column_id" 
                        where s."slot_id" = new."slot_id");
  -- 这里如果slot和column的关联是一对一，distinct可以去掉，减少计算
  
  -- 将子查询结果聚合为数组，存到变量中复用
  block.schedule_ids := (select array_agg(distinct c."schedule_id") 
                         from "slot_task" as st 
                         join "slot" as s on s."slot_id" = st."slot_id" 
                         join "column" as c on c."column_id" = s."column_id" 
                         where st."task_id" = new."task_id");
  
  -- 用= ANY()检查值是否在数组中
  if block.schedule_id = ANY(block.schedule_ids) then
    -- 这里写你的业务逻辑，比如允许插入/更新，或者抛出错误等
  end if;
  
  -- 触发器函数必须返回行，根据需求返回new或old
  return new;
end $$ language plpgsql;

方法二：将数组拆分为子查询后用IN #

如果你一定要保留IN的写法，可以通过unnest()函数把数组拆成行数据，再放到子查询中：

-- 替换判断逻辑部分
if block.schedule_id in (select unnest(block.schedule_ids)) then
  -- 你的业务逻辑
end if;

不过这种方法多了一层子查询的开销，性能不如第一种方法，只适合特定场景下使用。

另外补充个小提示：如果你的业务逻辑中，每个slot_id对应的schedule_id是唯一的，那第一个查询里的distinct可以直接去掉，能减少不必要的计算开销。

内容的提问来源于stack exchange，提问作者Kohányi Róbert