Hierarchical query conversion errors

[taptarap]

Hi, Darold.

There are several errors in converting hierarchical queries:

• extra ';' at the end of the query
• missing alias for subquery in the second part of result query (test4)
• the previous line is converted with the query if it does not end with ";" (test2, test3)

oracle_pkg_test.txt

after conversion:

CREATE OR REPLACE FUNCTION pkg_test.test1 (p_root_id bigint) RETURNS bigint AS $body$
DECLARE

    l_id bigint;

BEGIN
    SELECT MAX(id)
      INTO STRICT l_id
      FROM (WITH RECURSIVE cte AS (
SELECT t.*
              FROM tab1 t WHERE t.id = p_root_id
  UNION ALL
SELECT t.*
              FROM tab1 t JOIN cte c ON (c.id = t.root_id)

) SELECT * FROM cte;
) t;

    RETURN l_id;
  END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

CREATE OR REPLACE FUNCTION pkg_test.test2 (p_root_id bigint) RETURNS bigint AS $body$
DECLARE

    l_id bigint;WITH RECURSIVE cte AS (


BEGIN
    
    SELECT MAX(id)
      INTO STRICT l_id
      FROM tab1 t WHERE t.id = p_root_id
  UNION ALL

  
BEGIN
    
    SELECT MAX(id)
      INTO STRICT l_id
      FROM tab1 t JOIN cte c ON (c.id = t.root_id)

) SELECT * FROM cte;
;

    RETURN l_id;
  END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

CREATE OR REPLACE PROCEDURE pkg_test.test3 (p_rc INOUT REFCURSOR) AS $body$
WITH RECURSIVE cte AS (
BEGIN
    OPEN p_rc FOR
      SELECT t.*
        FROM tab1 t WHERE coalesce(t.id, 0) = 0
  UNION ALL
BEGIN
    OPEN p_rc FOR
      SELECT t.*
        FROM tab1 t JOIN cte c ON (c.id = t.root_id)

) SELECT * FROM cte;
;
  END;


$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

CREATE OR REPLACE PROCEDURE pkg_test.test4 (p_root_id bigint) AS $body$
DECLARE


  r_id RECORD;

BEGIN
    FOR r_id IN (WITH RECURSIVE cte AS (
SELECT t.*
                   FROM (SELECT *
                           FROM tab1
                          WHERE status = 1) t WHERE t.id = p_root_id
  UNION ALL
SELECT t.*
                   FROM (SELECT *
                           FROM tab1
                          WHERE status = 1) JOIN cte c ON (c.id = t.root_id)

) SELECT * FROM cte;
)
    LOOP
      UPDATE tab1 t
         SET status = 0
       WHERE id = r_id.id;
    END LOOP;
  END;


$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

CREATE OR REPLACE PROCEDURE pkg_test.test5 (p_root_id bigint) AS $body$
BEGIN
    UPDATE tab1 t
       SET status = 0
     WHERE id IN (WITH RECURSIVE cte AS (
SELECT t.id
                    FROM tab1 t WHERE t.id = p_root_id
  UNION ALL
SELECT t.id
                    FROM tab1 t JOIN cte c ON (c.id = t.root_id)

) SELECT * FROM cte;
);
  END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

ORACLE:

CREATE OR REPLACE PACKAGE BODY pkg_test IS
  FUNCTION test1(p_root_id IN NUMBER) RETURN NUMBER AS
    l_id NUMBER;
  BEGIN
    SELECT MAX(id)
      INTO l_id
      FROM (SELECT t.*
              FROM tab1 t
             START WITH t.id = p_root_id
            CONNECT BY nocycle PRIOR t.id = t.root_id) t;
  
    RETURN l_id;
  END;

  FUNCTION test2(p_root_id IN NUMBER) RETURN NUMBER AS
    l_id NUMBER;
  BEGIN
    
    SELECT MAX(id)
      INTO l_id
      FROM tab1 t
     START WITH t.id = p_root_id
    CONNECT BY nocycle PRIOR t.id = t.root_id;
  
    RETURN l_id;
  END;

  PROCEDURE test3(p_rc IN OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN p_rc FOR
      SELECT t.*
        FROM tab1 t
       START WITH nvl(t.id, 0) = 0
      CONNECT BY nocycle PRIOR t.id = t.root_id;
  END;

  PROCEDURE test4(p_root_id IN NUMBER) AS
  BEGIN
    FOR r_id IN (SELECT t.*
                   FROM (SELECT *
                           FROM tab1
                          WHERE status = 1) t
                  START WITH t.id = p_root_id
                 CONNECT BY nocycle PRIOR t.id = t.root_id)
    LOOP
      UPDATE tab1 t
         SET status = 0
       WHERE id = r_id.id;
    END LOOP;
  END;

  PROCEDURE test5(p_root_id IN NUMBER) AS
  BEGIN
    UPDATE tab1 t
       SET status = 0
     WHERE id IN (SELECT t.id
                    FROM tab1 t
                   START WITH t.id = p_root_id
                  CONNECT BY nocycle PRIOR t.id = t.root_id);
  END;

END pkg_test;

[darold]

Commit f47c4bf might fix this issue. Thanks for the tests cases.

[taptarap]

thanks Darold, the most important thing for me is working

but two minor errors are still here:

• missing alias for subquery in the second part of result query (test4)

SQL Error [42601]: ERROR: subquery in FROM must have an alias

WITH RECURSIVE cte AS (
SELECT t.*
                   FROM (SELECT *
                           FROM tab1
                          WHERE status = 1) t WHERE t.id = p_root_id
  UNION ALL
SELECT t.*
                   FROM (SELECT *
                           FROM tab1
                          WHERE status = 1) JOIN cte c ON (c.id = t.root_id)
) SELECT * FROM cte

• INTO in query (test2) But in this case, the transformation rules can be very complex. I think it's better to use subqueries like test1 in Oracle.

    WITH RECURSIVE cte AS (
SELECT MAX(id)
      INTO STRICT l_id
      FROM tab1 t WHERE t.id = p_root_id
  UNION ALL
SELECT MAX(id)
      INTO STRICT l_id
      FROM tab1 t JOIN cte c ON (c.id = t.root_id)

) SELECT * FROM cte;

[darold]

Commit 3e27abb should fix the INTO clause by moving it in the final SELECT of the CTE.

About the alias for the subquery it will not be fixed at least for the moment.