如何在PostgreSQL函数中递归调用自身并复用返回值？

以下是符合你需求的递归函数实现，通过函数自身递归调用实现多层级的边数据查询，替代原有的固定两层UNION逻辑：

修正后的函数代码 #

create or replace function get_film_count(a varchar[], v varchar[])
 RETURNS Table(costs decimal, id varchar, in_node varchar[], out_node varchar[], cycle BOOLEAN, visited_list varchar[])
 language plpgsql
 as
 $$
DECLARE
  new_a varchar[];
  new_v varchar[];
begin
  -- 返回当前层级符合条件的边数据
  RETURN QUERY
    SELECT edg.costs, edg.id, ARRAY[edg.in_node] AS in_node, ARRAY[edg.out_node] AS out_node,
           edg.in_node = ANY(v) AS cycle, v || ARRAY[edg.in_node] AS visited_list
    FROM edges edg
    WHERE edg.in_node = ANY(a) AND edg.in_node != ANY(v);

  -- 收集当前层级边的out_node作为下一轮递归的起始节点，同时更新已访问列表
  SELECT array_agg(DISTINCT edg.out_node), v || array_agg(DISTINCT edg.in_node)
  INTO new_a, new_v
  FROM edges edg
  WHERE edg.in_node = ANY(a) AND edg.in_node != ANY(v);

  -- 递归终止条件：当没有新的起始节点时停止递归
  IF new_a IS NOT NULL AND array_length(new_a, 1) > 0 THEN
    RETURN QUERY
      SELECT * FROM get_film_count(new_a, new_v);
  END IF;
end;
$$;

-- 测试调用
select * from get_film_count(ARRAY['a'], ARRAY['']);

关键修改说明 #

• 递归终止条件：通过判断new_a是否为空数组，避免无限递归调用，确保遍历到没有后续节点时停止。
• 参数传递逻辑：从当前层级的查询结果中，用array_agg收集所有out_node作为下一轮递归的起始节点数组new_a，同时将当前层级的in_node合并到已访问列表new_v中，防止重复访问节点。
• 语法修正：使用PL/pgSQL标准的SELECT ... INTO语法完成变量赋值，替代你原代码中错误的变量赋值方式。
• 结果合并：通过两次RETURN QUERY自动合并当前层级结果与递归调用的结果，实现多层级数据的聚合返回。

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