From: | Jian He <hejian(dot)mark(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle |
Date: | 2021-10-17 08:36:47 |
Message-ID: | CAMV54g0xEw_xoj0p2hmDio6fN5bPyybSp0oXOntrpzAN_FUOhg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2021-10-17 10:53:56 | Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle |
Previous Message | Steve Midgley | 2021-10-14 15:19:17 | Re: What is the regex for apostraphe in postgres? |