menu sample, PostgreSQL

menu sample

CREATE TABLE MENU
(
 ID integer,
 LABEL character varying(255),
 PARENT integer,
 PRIMARY KEY(ID)
);
INSERT INTO menu (id, label, parent ) VALUES(1, 'PHP', 0);
  
INSERT INTO menu (id, label, parent ) VALUES(2, 'Tutorials', 1);
  
INSERT INTO menu (id, label, parent ) VALUES(3, 'Scripts', 1);
  
INSERT INTO menu (id, label, parent ) VALUES(4, 'Arrays', 2);
  
INSERT INTO menu (id, label, parent ) VALUES (5, 'Operators', 2);
  
INSERT INTO menu (id, label, parent ) VALUES (6, 'Arithmetic operators', 5);
  
INSERT INTO menu (id, label, parent ) VALUES (7, 'Assignment operators', 5);
  
INSERT INTO menu (id, label, parent ) VALUES (8, 'Java', 0);
  
INSERT INTO menu (id, label, parent ) VALUES (9, 'Tutorials', 8);
  
INSERT INTO menu (id, label, parent ) VALUES (10, 'Programs', 8);
  
INSERT INTO menu (id, label, parent ) VALUES (11, 'JavaScript', 0);
  
INSERT INTO menu (id, label, parent ) VALUES (12, 'MySQL', 0);
  
INSERT INTO menu (id, label, parent ) VALUES (13, 'CSS', 0);
  
INSERT INTO menu (id, label, parent ) VALUES (14, 'Tutorials', 13);
  
INSERT INTO menu (id, label, parent ) VALUES (15, 'Servlet', 9);
  
INSERT INTO menu (id, label, parent ) VALUES(16, 'JSP', 9);
select * from MENU;

with recursive X (id, parent, arrPath) as
(
select id, parent, array [label]
from menu
where parent = 0
union all
select b.id, b.parent,
X.arrPath ||  b.label
from X, menu b
where X.id = b.parent
)
select id,parent,array_upper (arrPath, 1) as Level, '/'||REPLACE(lower(array_to_string (arrPath, '/')), ' ', '_') as path
from X order by  arrPath;


run \| edit \| history \| help	0