Seaching over a graph with recursive queries (Postgres)

graph.md

CREATE TEMP TABLE prod_comps (
  prod_id VARCHAR(20),
  comp_id VARCHAR(20)
);

INSERT INTO prod_comps (prod_id, comp_id) VALUES ('Prod', 'B');
INSERT INTO prod_comps (prod_id, comp_id) VALUES ('Prod', 'C');
INSERT INTO prod_comps (prod_id, comp_id) VALUES ('B', 'D');
INSERT INTO prod_comps (prod_id, comp_id) VALUES ('B', 'E');
INSERT INTO prod_comps (prod_id, comp_id) VALUES ('E', 'F');
INSERT INTO prod_comps (prod_id, comp_id) VALUES ('F', 'G');
INSERT INTO prod_comps (prod_id, comp_id) VALUES ('G', 'H');

WITH RECURSIVE search_graph(prod_id, comp_id, depth) AS (
        SELECT p.prod_id, p.comp_id, 1
        FROM prod_comps p
      UNION ALL
        SELECT sg.prod_id, p.comp_id, sg.depth + 1
        FROM prod_comps p, search_graph sg
        WHERE p.prod_id = sg.comp_id
)
SELECT comp_id FROM search_graph where prod_id = 'Prod';
 comp_id
---------
 B
 C
 E
 D
 F
 G
 H
(7 rows)

SELECT comp_id FROM search_graph where prod_id = 'E';
 comp_id
---------
 F
 G
 H
(3 rows)

• Recursive Queries in postgres link
• Graph editor link