agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedDoes PostgreSQL have a pseudo-column like "LEVEL" in Oracle
3+ messages / 3 participants
[nested] [flat]
* Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle
@ 2021-10-17 08:36 Jian He <hejian.mark@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jian He @ 2021-10-17 08:36 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
https://stackoverflow.com/questions/22626394/does-postgresql-have-a-pseudo-column-like-level-in-orac...
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle
@ 2021-10-17 10:53 Thomas Kellerer <shammat@gmx.net>
parent: Jian He <hejian.mark@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Thomas Kellerer @ 2021-10-17 10:53 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Jian He schrieb am 17.10.2021 um 10:36:
> https://stackoverflow.com/questions/22626394/does-postgresql-have-a-pseudo-column-like-level-in-orac...
> 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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle
@ 2021-10-17 20:42 Simon Riggs <simon.riggs@enterprisedb.com>
parent: Jian He <hejian.mark@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Simon Riggs @ 2021-10-17 20:42 UTC (permalink / raw)
To: Jian He <hejian.mark@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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-orac...
> 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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-10-17 20:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-17 08:36 Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle Jian He <hejian.mark@gmail.com>
2021-10-17 10:53 ` Thomas Kellerer <shammat@gmx.net>
2021-10-17 20:42 ` Simon Riggs <simon.riggs@enterprisedb.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox