PostgreSQL递归CTE链式查询中如何对起始值进行参数化？

以下是两种常用的简便实现方案：

方案1：单独定义参数CTE（无需额外封装，适配预编译传参） #

把参数单独放在一个非递归CTE中作为数据源，直接给递归CTE的锚点成员调用，也可以直接对接应用层的预编译参数占位符：

WITH RECURSIVE
-- 参数定义区，替换占位符即可修改起始值
params(start_val) AS (SELECT ?::text), 
chain(from_id, to_id) AS (
  -- 锚点直接取参数CTE的起始值
  SELECT NULL, start_val FROM params
  UNION ALL
  SELECT c.to_id, ani.v2
  FROM chain c
  LEFT JOIN table1 ani ON ani.v1 = c.to_id
  WHERE c.to_id IS NOT NULL
)
SELECT to_id FROM chain;

如果是手动执行，把?替换成具体的起始值比如'vc1'即可。

方案2：封装为SQL函数（适合高频复用场景） #

如果需要频繁调用这个递归查询，可以封装为自定义SQL函数，调用时直接传参即可：

-- 创建函数
CREATE OR REPLACE FUNCTION get_chain(start_node text)
RETURNS TABLE(node_val text) AS $$
WITH RECURSIVE chain(from_id, to_id) AS (
  SELECT NULL, start_node
  UNION ALL
  SELECT c.to_id, ani.v2
  FROM chain c
  LEFT JOIN table1 ani ON ani.v1 = c.to_id
  WHERE c.to_id IS NOT NULL
)
SELECT to_id FROM chain;
$$ LANGUAGE sql STABLE;

-- 调用示例
SELECT * FROM get_chain('vc1');

原错误写法原因说明 #

• 第一种在锚点成员中写SELECT NULL, where to_id = ?的问题：锚点是递归的初始数据集，此时还没有生成to_id字段，无法直接过滤
• 第二种在外层加WHERE to_id='vc1'的问题：是先完成全量递归后再过滤结果，不是从指定节点开始递归，因此无法得到预期的链式关联结果

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