postgres中的递归查询，参数为chain

Question

CREATE TABLE table1
("v1" text, "v2" text)
;

INSERT INTO table1
("v1", "v2")
VALUES
('vc1', 'vc2'),
('vc2', 'vc3'),
('vc3', 'vc4'),
('vc4', 'rc7')
;

我有这个查询

WITH RECURSIVE chain(from_id, to_id) AS (
SELECT NULL, 'vc1'
UNION
SELECT c.to_id, ani.v2
FROM chain c
LEFT JOIN table1 ani ON (ani.v1 = to_id)
WHERE c.to_id IS NOT NULL
)
SELECT to_id FROM chain;

我已经尝试在cte中参数化'vc1'，但遇到了困难，并且在文档中没有看到任何关于此的在线内容。

我试过了

SELECT NULL, where to_id = ?

和

SELECT to_id from chain where to_id='vc1';

是否有一个简单的方法来插入一个参数到cte?

Answer 1

可以在CTE的根/锚查询中提供该参数。但是您需要从递归部分的实际表中进行选择:

WITH RECURSIVE chain AS (
select "applicationNodeInstanceId", "nextNodeInstanceId"
from appnodeinstance     
where "applicationNodeInstanceId" = 'vc1' --<< this can be a parameter
UNION all
SELECT c."applicationNodeInstanceId", c."nextNodeInstanceId"
FROM appnodeinstance c
JOIN chain ani ON c."applicationNodeInstanceId" = ani."nextNodeInstanceId"
)
SELECT * 
FROM chain;