From: | Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> |
---|---|
To: | Jian He <hejian(dot)mark(at)gmail(dot)com> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle |
Date: | 2021-10-17 20:42:39 |
Message-ID: | CANbhV-HTtiGER23Fsw_LK3iGarBttPp6-zzYYxZ8vNnhYbY8-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sun, 17 Oct 2021 at 09:37, Jian He <hejian(dot)mark(at)gmail(dot)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 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?
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/
From | Date | Subject | |
---|---|---|---|
Next Message | aditya desai | 2021-10-18 16:42:52 | Query out of memory |
Previous Message | Thomas Kellerer | 2021-10-17 10:53:56 | Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle |