cycle.sql

DROP TABLE comment;

CREATE TABLE comment (
    comment_id INTEGER PRIMARY KEY
    ,body TEXT
    ,parent_id INT REFERENCES comment (comment_id)
);

DELETE FROM comment;

INSERT INTO comment 
	(comment_id, body, parent_id)
VALUES
	(1, 'one', 5)
	,(2, 'two', 1)
	,(3, 'three', 2)
	,(4, 'four', 1)
	,(5, 'five', 3)
	,(6, 'six', 4)
	,(7, 'seven', 2)
ON CONFLICT (comment_id) DO NOTHING
;

WITH pkey_ (comment_id) AS (
    VALUES (5)
)
,cycle1_ (comment_id, parent_id) AS (
    SELECT comment.comment_id, comment.parent_id
    FROM comment
    JOIN pkey_ ON pkey_.comment_id = comment.comment_id
    UNION
    SELECT comment.comment_id, comment.parent_id
    FROM comment 
    JOIN cycle1_ ON cycle1_.parent_id = comment.comment_id
)
SELECT
    comment.comment_id
    ,comment.body
    ,comment.parent_id
FROM comment
JOIN cycle1_ ON cycle1_.comment_id = comment.comment_id
;