Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle

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;