Geting rows from self-referencing single table using Postgres I am trying to get rows from self-referencing single table in Postgres.



I got table A and I would like to get Table B. For given ProcID using Input_proc_Id I want to get another row until i get capture row 'YES'. So it's kind of loop that will append row to new table until it get 'Yes' in capture filed.  

Is it a way to do it in single query or i need to create function loop? If so how to do it?

Setup code:

CREATE TABLE table_A AS
  SELECT 1 AS procid, 3 AS input_proc_id, false AS capture
  UNION ALL SELECT 2 AS procid, 4 AS input_proc_id, false AS capture
  UNION ALL SELECT 3 AS procid, 6 AS input_proc_id, false AS capture
  UNION ALL SELECT 4 AS procid, 6 AS input_proc_id, false AS capture
  UNION ALL SELECT 5 AS procid, 7 AS input_proc_id, false AS capture
  UNION ALL SELECT 6 AS procid, 8 AS input_proc_id, false AS capture
  UNION ALL SELECT 7 AS procid, 8 AS input_proc_id, false AS capture
  UNION ALL SELECT 8 AS procid, 9 AS input_proc_id, false AS capture
  UNION ALL SELECT 9 AS procid, 10 AS input_proc_id, false AS capture
  UNION ALL SELECT 10 AS procid, 12 AS input_proc_id, false AS capture
  UNION ALL SELECT 11 AS procid, 13 AS input_proc_id, false AS capture
  UNION ALL SELECT 12 AS procid, 14 AS input_proc_id, false AS capture
  UNION ALL SELECT 13 AS procid, 15 AS input_proc_id, false AS capture
  UNION ALL SELECT 14 AS procid, null AS input_proc_id, true AS capture
  UNION ALL SELECT 15 AS procid, null AS input_proc_id, true AS capture;
Thank's too @dezso comments i found an answer how to do it!

WITH RECURSIVE temp(procid, input_proc_id, capture) AS (
  SELECT procid, input_proc_id, capture
  FROM table_A
  WHERE procid = 1
  UNION ALL
    SELECT table_A.procid, table_A.input_proc_id, table_A.capture
  FROM temp, table_A
  WHERE  temp.input_proc_id = table_A.procid
)

SELECT * FROM temp;


Which for given "procid" gives correct output, but it works on assumption that input_proc_id is null when "capture" column is "YES" otherwise it is not going to work.