How to use recursive CTEs to get a hierarchy in tree form?

user_16157204 0
Viewed 167

The table has the column 'description' with the names of grandparents, parents and childen. I need to get the hierarchy of these values in tree form when using recursive CTE in PostgreSQL.

| id | description  | code |
| 1  | Grandparent1 | null |
| 2  | Parent1      | 1    |
| 3  | parent2      | 1    |
| 4  | child        | 2    |
| 5  | Sandy        | 2    |
| 5  | Peter        | 3    |
         .
         .
         .

This is my code:

WITH RECURSIVE alias AS (
SELECT 
id,
description,
code,
CAST (description AS TEXT) as result
FROM table_name
WHERE code IS NULL

UNION ALL

SELECT
e.id,
e.description,
e.code,
CAST (a.result || '  -->  ' || e.description AS TEXT) AS result
FROM
table_name e INNER JOIN alias a
    ON (e.code = a.id)
)
SELECT
result
FROM
alias

And this is the result I get:

 |result                                |
    Granparent1
    Granparent1 --> Parent1
    Granparent1 --> Parent1 --> Child
    Granparent1 --> Parent1 --> Sandy
    Granparent1 --> Parent2
    Granparent1 --> Parent2 --> Peter
    Granparent2
    Granparent2 --> Parent21
    Granparent2 --> Parent21 --> Robert

Nevertheless, I need to get almost the same result but in this form:

|result                                |
Granparent1
    --> Parent1
           --> Child
           --> Sandy
    --> Parent2
           --> Peter
Granparent2
    --> Parent21
           --> Robert
recursive-query tree recursion common-table-expression postgresql
1 Answers

You could add a level field to the recursive query, which can be translated to an indent. And to keep the order right, it may be better to concatenate the id values using to_char(id, '0000000000'): the width of that format string should be wide enough to cover for the greatest id value in your table.

with recursive cte as (
    select 1 as level,
           to_char(id, '0000000000') as ord,
           id, 
           cast(description as varchar) result 
    from   table_name
    where  code is null
    union
    select cte.level + 1,
           ord || to_char(table_name.id, '0000000000'),
           table_name.id, 
           repeat('.', cte.level * 4) || '--> ' || cast(table_name.description as varchar)
    from   table_name
    inner join cte 
        on table_name.code = cte.id
)
select   result
from     cte
order by ord;
user_54598390
Related