From 'start with' and 'connect by' in Oracle to Postgres

Question

UPD:

I want to convert this Oracle code:

select lpad(c4, length(c4) + (level*2)-2 , ' '), lpad(c2, length(c2) + (level*2)-2 , ' ')
from (select root_id c1, root_tab_col_name c2, null c3, 'root_table' c4 from root_table
    union all
    select second_tab_id, second_tab_col_name, root_tab_id, 'second_table' from second_table
    union all
    select third_tab_id, third_tab_col_name, second_tab_id, 'third_table' from third_table)
start with c3 is null
connect by prior c1 = c3;

to PostgreSQL form. Could someone help me? With regards.

UPD:

I have 3 tables connected with foreign keys:

Result of this query:

Answer 1

try this :

with request_base as (
       select root_id c1, root_tab_col_name c2, null c3, 'root_table' c4 from root_table
    union all
       select second_tab_id, second_tab_col_name, root_tab_id, 'second_table' from second_table
     union all
    select third_tab_id, third_tab_col_name, second_tab_id, 'third_table' from third_table
)
,req2 as (WITH recursive recurs1(tables_name,texts,n) as (
      select c4,c2,c1 rel from request_base where c3 is null
    UNION 
        select c4,c2,c1 from request_base,recurs1 where c3=n
 )
    select tables_name,texts from recurs1
)
select * from req2