0

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

Tables

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;
  • 1
    And, please, sample data as text, not as an image. – Colin 't Hart Jun 4 '18 at 13:40
1

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.

  • It won't stop at "Yes" though. – ypercubeᵀᴹ Jun 5 '18 at 7:36
  • True, it works only if "input_proc_id" is null when "capture" column is "YES" so I agree it's not fully correct answer. – Losbaltica Jun 5 '18 at 10:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.