I'm trying to convert this code from Oracle database to Postgresql, can someone help me with that?

WITH cycles AS
(
    SELECT SYS_CONNECT_BY_PATH (child_node_id, ',') AS path, SYS_CONNECT_BY_PATH ( CASE WHEN child_node_id < CONNECT_BY_ROOT child_node_id THEN child_node_id END, ','
) AS less_path
FROM alf_child_ASSOC
WHERE CONNECT_BY_ROOT parent_node_id = child_node_id
CONNECT BY NOCYCLE parent_node_id = PRIOR child_node_id
)

SELECT *
FROM cycles
WHERE LTRIM (less_path, ',') IS NULL ;

Thanks

CodePudding user response：

You can convert your Oracle query from a hierarchical query to a recursive query in the form:

WITH 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

Oracle db<>fiddle here

Then all you need to do to convert to PostgreSQL is to add the RECURSIVE keyword:

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

Which, for the sample data:

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);
INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (3, 2);
INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (1, 3);

Outputs:

PATH
,1,2,3

PostgreSQL db<>fiddle here

CodePudding user response：

I found a working solution:

WITH RECURSIVE prev AS (
        SELECT alf_child_ASSOC.child_node_id, 1 AS depth, array[child_node_id] as seen, false as cycle
        FROM alf_child_ASSOC
        UNION ALL
        SELECT alf_child_ASSOC.child_node_id, prev.depth   1, seen || alf_child_ASSOC.child_node_id as seen,
            alf_child_ASSOC.child_node_id = any(seen) as cycle
        FROM prev
        INNER JOIN alf_child_ASSOC on prev.child_node_id = parent_node_id
        AND prev.cycle = false
    )
    SELECT *
    FROM prev
    WHERE cycle = true;

Exemple of output:

 child_node_id | depth |       seen        | cycle
--------------- ------- ------------------- -------
        749254 |     2 | {749254,749254}   | t
        749675 |     2 | {749675,749675}   | t
        754208 |     2 | {754208,754208}   | t