Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE alf_child_assoc (
  child_node_id  NUMERIC(10,0),
  parent_node_id NUMERIC(10,0)
);
INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (2, 1);
1 rows affected

INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (3, 2);
1 rows affected

INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (1, 3);
1 rows affected

WITH RECURSIVE cycles (path, root_id, child_node_id) AS (
  SELECT ',' || child_node_id,
         parent_node_id AS root_id,
         child_node_id
  FROM   alf_child_assoc
  WHERE  parent_node_id > child_node_id
UNION ALL
  SELECT c.path || ',' || a.child_node_id,
         c.root_id,
         a.child_node_id
  FROM   cycles c
         INNER JOIN alf_child_assoc a
         ON (c.child_node_id = a.parent_node_id)
  WHERE  a.parent_node_id != c.root_id
)
SELECT path
FROM   cycles
WHERE  child_node_id = root_id


path


,1,2,3