Thread: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle
Wandering around, playing around, then problems come. I tried to crack the level concept . So I followed through with the most voted answer from the above link. The following is my code sample data.
begin;
create temp table tempemp (employee_id integer primary key, last_name text,manager_id integer);
insert into tempemp values(1,'eliane',1);
insert into tempemp values(2,'sponge',1);
insert into tempemp values(3,'george',1);
insert into tempemp values(4,'kramer',2);
insert into tempemp values(5,'megan',2);
insert into tempemp values(6,'donald',3);
commit;
WITH RECURSIVE cte AS (
SELECT employee_id, last_name, manager_id, 1 AS level
FROM tempemp
UNION ALL
SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1
FROM cte c
JOIN tempemp e ON e.manager_id = c.employee_id
)
SELECT *
FROM cte;
But when I execute the code, It seems like an infinite loop. So, any suggestions(code sample) for me to crack the level concept?
Jian He schrieb am 17.10.2021 um 10:36: > https://stackoverflow.com/questions/22626394/does-postgresql-have-a-pseudo-column-like-level-in-oracle > Wandering around, playing around, then problems come. I tried to > crack the *level *concept . So I followed through with the most voted > answer from the above link. The following is my code sample data. > > begin; > create temptable tempemp (employee_idinteger primary key, last_name text,manager_idinteger); > insert into tempempvalues(1,'eliane',1); > insert into tempempvalues(2,'sponge',1); > insert into tempempvalues(3,'george',1); > insert into tempempvalues(4,'kramer',2); > insert into tempempvalues(5,'megan',2); > insert into tempempvalues(6,'donald',3); > commit; > > WITH RECURSIVE cteAS ( > SELECT employee_id, last_name, manager_id,1 AS level > FROM tempemp > > UNION ALL > SELECT e.employee_id, e.last_name, e.manager_id, c.level +1 > FROM cte c > JOIN tempemp eON e.manager_id = c.employee_id > ) > SELECT * > FROM cte; > This row: > insert into tempempvalues(1,'eliane',1); creates an endless loop because it points to itself. If there is no manager assigned you should use NULL instead Additionally your recursive CTE does not have a condition for the starting element If you want to stick with the circular reference of an employee to itself, you need to exlcude the starting element WITH RECURSIVE cte AS ( SELECT employee_id, last_name, manager_id, 1 AS level FROM tempemp where employee_id = 1 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1 FROM tempemp e JOIN cte c ON e.manager_id = c.employee_id where e.employee_id <> 1 ) SELECT * FROM cte; But it would be better to use: insert into tempempvalues(1,'eliane', null); Then you don't need to exclude the root element in the recursive part: WITH RECURSIVE cte AS ( SELECT employee_id, last_name, manager_id, 1 AS level FROM tempemp where manager_id is null UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1 FROM tempemp e JOIN cte c ON e.manager_id = c.employee_id ) SELECT * FROM cte;
On Sun, 17 Oct 2021 at 09:37, Jian He <hejian.mark@gmail.com> wrote: > > > https://stackoverflow.com/questions/22626394/does-postgresql-have-a-pseudo-column-like-level-in-oracle > Wandering around, playing around, then problems come. I tried to crack the level concept . So I followed through withthe most voted answer from the above link. The following is my code sample data. > > begin; > create temp table tempemp (employee_id integer primary key, last_name text,manager_id integer); > insert into tempemp values(1,'eliane',1); > insert into tempemp values(2,'sponge',1); > insert into tempemp values(3,'george',1); > insert into tempemp values(4,'kramer',2); > insert into tempemp values(5,'megan',2); > insert into tempemp values(6,'donald',3); > commit; > > WITH RECURSIVE cte AS ( > SELECT employee_id, last_name, manager_id, 1 AS level > FROM tempemp > > UNION ALL > SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1 > FROM cte c > JOIN tempemp e ON e.manager_id = c.employee_id > ) > SELECT * > FROM cte; > > But when I execute the code, It seems like an infinite loop. So, any suggestions(code sample) for me to crack the levelconcept? Add a path array to track the graph as it grows, allowing you to exclude already visited nodes. WITH RECURSIVE cte AS ( SELECT employee_id, last_name, manager_id, 1 AS level, ARRAY[employee_id] as path FROM tempemp UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1, path || e.employee_id FROM cte c JOIN tempemp e ON e.manager_id = c.employee_id WHERE e.employee_id <> all (c.path) ) SELECT employee_id, last_name, manager_id, level FROM cte; With the above query, it doesn't matter if the data contains loops. This adds no appreciable execution time - the above executes in 0.2ms, yet avoids infinite loops. -- Simon Riggs http://www.EnterpriseDB.com/