agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle
Date: Sun, 17 Oct 2021 12:53:56 +0200
Message-ID: <205bcd0b-d74b-b686-ec31-76a04c279a6d@gmx.net> (raw)
In-Reply-To: <CAMV54g0xEw_xoj0p2hmDio6fN5bPyybSp0oXOntrpzAN_FUOhg@mail.gmail.com>
References: <CAMV54g0xEw_xoj0p2hmDio6fN5bPyybSp0oXOntrpzAN_FUOhg@mail.gmail.com>



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;

view thread (3+ messages)  latest in thread

Message-ID: <205bcd0b-d74b-b686-ec31-76a04c279a6d@gmx.net>
Permalink:  ../205bcd0b-d74b-b686-ec31-76a04c279a6d@gmx.net/
Also on:    postgresql.org/message-id/205bcd0b-d74b-b686-ec31-76a04c279a6d@gmx.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: shammat@gmx.net, pgsql-sql@lists.postgresql.org
  Subject: Re: Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle
  In-Reply-To: <205bcd0b-d74b-b686-ec31-76a04c279a6d@gmx.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox