recursive query in postgres with a parameter in chain

Question

with this table

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

I have this query

    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;

I have tried making the 'vc1' paramaterized in the cte, but have had difficulty and seen nothing online about this, in docs or so.

I have tried

SELECT NULL, where to_id = ?

And then

SELECT to_id from chain where to_id='vc1';

Is there an easy way to insert a parameter into the cte?

Answer 1

You can provide the parameter in the root/anchor query of the CTE. But you need to select from the actual table in the recursive part:

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;