Oracle connect by function alternate in Postgres-CodePudding

I have below query in oracle, I want to migrate into Postgres, Can anyone please help me on that?

The only challenging part is Connect by command. Its not supported in Postgres

  WITH readyForHouseKeeper AS (
  SELECT regexp_substr(system_file_path, '^.*/') AS path
  FROM cognito.patent p
  JOIN cognito.pat_tracking pt
  ON  p.ref_id = pt.pat_ref_id
  AND p.language = 'es'
  AND p.stage_id=80
  AND pt.stage_id=80
  AND pt.status_date BETWEEN (localtimestamp - INTERVAL '2' MONTH) AND (localtimestamp - INTERVAL '1' MONTH) -- between 60days and 30 days -- Frontfile
  AND rownum < 50001
  ORDER BY pt.status_date
)
SELECT h.path||' '|| fileType ||' 30 * f rm'
FROM readyForHouseKeeper h, 
      (
        SELECT trim(regexp_substr(fileType,'[^;] ', 1, LEVEL)) AS fileType                                         
        FROM ( SELECT 'stripped_cxml_*;MachineOutput_NameFile_cxml_*;Low_Confidence_*;*ContentFile*' AS fileType) 
        CONNECT BY instr(fileType, ';', 1, LEVEL - 1) > 0
      )

CodePudding user response：

No need for connect by or a recursive query, just turn the string into rows using string_to_table()

....
SELECT h.path||' '|| t.fileType ||' 30 * f rm'
from readyForHouseKeeper h,
  lateral string_to_table('stripped_cxml_*;MachineOutput_NameFile_cxml_*;Low_Confidence_*;*ContentFile*', ';') as t(filetype)

Although I prefer an explicit cross join:

SELECT h.path||' '|| t.fileType ||' 30 * f rm'
from readyForHouseKeeper h --<< no comma!
  cross join lateral string_to_table(...) as t(filetype)

Or if you are using an older Postgres version:

....
SELECT h.path||' '|| t.fileType ||' 30 * f rm'
from readyForHouseKeeper h,
  lateral unnest(string_to_array('stripped_cxml_*;MachineOutput_NameFile_cxml_*;Low_Confidence_*;*ContentFile*', ';')) as t(filetype)