postgresql example hierarchy with addresses, PostgreSQL

postgresql example hierarchy with addresses

CREATE TABLE Address
    (
    address_id int NOT NULL CONSTRAINT taxPkey PRIMARY KEY,
    value VARCHAR(255),
    parent_id int
    );

/*
insert into Address 
values (1, 'Europe',0),
         (2, 'Ukraine',1),
             (4, 'Kyiv region',2),
                 (5, 'Kyiv city',4),
                     (7, 'Radyscheva',5),
             (10, 'Cherkassy region',2) ,
         (3, 'Poland',1),
             (6, 'Krakow region',3),
                 (8, 'Krakow city',6),
             (9, 'Vrozlav region',3)
  ;
     
*/

insert into Address 
values (1, 'Europe',0),
       (7, 'Radyscheva',5),
       (3, 'Poland',1),
       (2, 'Ukraine',1),
       (6, 'Krakow region',3),
       (8, 'Krakow city',6),
       (4, 'Kyiv region',2),
       (5, 'Kyiv city',4),
       (10, 'Cherkassy region',2) ,
       (9, 'Vrozlav region',3)
  ;

select * from address;

WITH RECURSIVE cte AS (
   SELECT  ARRAY[address_id] AS hierarchy_order,
    address_id, value, 0 AS level,  parent_id as parent_node 
   FROM   Address
   WHERE  parent_id = 0

UNION  ALL
   
   SELECT c.hierarchy_order || t.address_id as hierarchy_order,
    t.address_id, t.value as v , c.level + 1 as level, parent_id as parent_node
   FROM   cte      c
   JOIN   Address t ON t.parent_id = c.address_id
   )
SELECT address_id, 
--lpad('> '||value,level+2+length(value),'-') as val,
lpad(value,level+length(value)) as value,
parent_node, level
FROM   cte
ORDER  BY  hierarchy_order;


run \| edit \| history \| help	0