sql - Unable to find alternative for connect by level in postgresql

Question

select 
trim(regexp_substr(t.column_A, '[^,]+', 1, levels.column_value)) as column_A 

from (SELECT * FROM table_t t ) t, 

table(cast(multiset(select level from dual 
connect by level <= length (regexp_replace(t.column_A, '[^,]+')) + 1) as sys.OdciNumberList)) levels

Not able to find alternate for connect by level in postgresql. Also the dual don't exist in postgresql.

It would be great help if someone help me to identify alternative for the above posted query.

question from:https://stackoverflow.com/questions/65939402/unable-to-find-alternative-for-connect-by-level-in-postgresql

Answer 1

You did not define your table_t so I will assume that column_a is a comma separated string as that appears to be what the Oracle is looking for. If that is correct then perhaps the simplest approach is to transform the string to an array, then unroll the array. Fortunately, Postgres provides single function for each action string_to_array and unnest respectively. (See documentation: Array Functions and Operators).

with table_t( column_a) as
     ( values ( 'abc,defgh,ijklm,nopqr,stuvw,zyz0123,456789'))
select unnest (string_to_array(column_a,','))
  from table_t;