Friday, 20 October 2017

Cycle Handling in PostGreSQL

Below is the way to handle Cycle data in Postgresql database

 CREATE TABLE FOLDER(ID INT , NAME VARCHAR(255), PARENT INT);

INSERT INTO FOLDER VALUES(1, '/', null);
INSERT INTO FOLDER VALUES(2, 'src', 1);
INSERT INTO FOLDER VALUES(3, 'main', 2);
INSERT INTO FOLDER VALUES(4, 'org', 3);
INSERT INTO FOLDER VALUES(5, 'test', 2);


With RECURSIVE CTE(Id,NAME,Parent,Pathh,path, cycle)
as
(
Select Id,NAME,parent, cast(Id  as varchar(30)) pathh,ARRAY[Folder.Id] path,false from Folder
where parent is null
union all
Select 
F.id,F.name,F.Parent,cast(F.Id||'-->'||C.Pathh as varchar(30))  pathh,
C.Path||F.id path,F.id = any(path) as cycle
from 
Folder F,CTE C
where F.Parent=C.ID  and not cycle
)
Select C.* from CTE C
order by 1 


No comments:

Post a Comment