Recursive common table expressions (CTE) for arbitrary depth hierarchies

The challenge of storing an arbitrary depth hierarchy isn’t so much with the storing of it as it is with the querying it and rendering it in a useful fashion.

Given a simple hierarchy stored in a table that has a self-referential parent_id such as:

create table hierarchy (
 id integer NOT NULL,
 parent_id integer,
 name varchar,
 CONSTRAINT pk1 PRIMARY KEY (id),
 CONSTRAINT fk_self FOREIGN KEY (parent_id)
 REFERENCES hierarchy (id)
);

With some sample data:

 
insert into hierarchy 
 (id,parent_id,name) 
values 
 (1,null,'A')
 ,(2,1,'B')
 ,(3,1,'C')
 ,(4,1,'D')
 ,(5,2,'E')
 ,(6,2,'F')
 ,(7,6,'G')
 ,(8,6,'H')
 ,(9,7,'I')
 ,(10,7,'J')
 ,(11,4,'K')
 ,(12,4,'L')
 ,(13,11,'M')
;

We can extract the data:

with recursive hierarchy_cte (id,parent_id,name, lvl,lineage) as
(
 /* base query */
 select 
 id
 , parent_id
 , name
 , 0 lvl
 , name lineage
 from 
 hierarchy
 where 
 parent_id is null
 
 union all
 
 /* recursing query */
 select 
 h.id
 , h.parent_id
 , h.name
 , lvl+1
 , concat(lineage, ' ', h.name) lineage
 from 
 hierarchy h 
 inner join hierarchy_cte c on 
 h.parent_id=c.id
)
/* reporting query */ 
select 
 concat(repeat('-',lvl * 4),Name) Tree
 ,hc.* 
from 
 hierarchy_cte hc
order by 
 lineage
 , name

Which yields the following results: