I
I
Ivan_R22020-10-27 20:03:08
PostgreSQL
Ivan_R2, 2020-10-27 20:03:08

How to assign values ​​from recursive fetch in PostgreSQL?

Hello!
There is a query that returns a result in the form

id | manager_id | level | star_level 
----+------------+-------+------------
  1 |       NULL |     1 |          0
  2 |          1 |     2 |          1
  3 |          2 |     3 |          1
  4 |          3 |     4 |          2
  5 |          4 |     5 |          2
  6 |          5 |     6 |          2
  7 |          6 |     7 |          3
  8 |          7 |     8 |          3
  9 |          8 |     9 |          4
(9 rows)


The request itself:
WITH RECURSIVE parents AS (
  SELECT e.id
       , e.manager_id
       , 1 AS level
       , CAST(s.is_star AS INTEGER) AS star_level
    FROM employees AS e
         INNER JOIN skills AS s
            ON e.skill_id = s.id
   WHERE manager_id IS NULL
UNION ALL
  SELECT e.id
       , e.manager_id
       , p.level + 1 AS level
       , p.star_level + CAST(s.is_star AS INTEGER) AS star_level
    FROM employees AS e
         INNER JOIN skills AS s
            ON e.skill_id = s.id
         INNER JOIN parents AS p
            ON e.manager_id = p.id
   WHERE e.manager_id = p.id
)
SELECT *
  FROM parents
;

Can you please tell me how to change the query in order to immediately record the level and star_level columns in it?

Demo data:
create table Employees(
    id INT,
    name VARCHAR,
    manager_id INT,
    skill_id INT,
    level INT,
    star_level INT
    
);

 create table Skills(
    id INT,
    name VARCHAR,
    is_star BOOL
);

INSERT INTO Employees
    (id, name, manager_id, skill_id)
VALUES
    (1, 'Employee 1', NULL, 1),
    (2, 'Employee 2', 1, 2),
    (3, 'Employee 3', 2, 3),
    (4, 'Employee 4', 3, 4),
    (5, 'Employee 5', 4, 5),
    (6, 'Employee 6', 5, 1),
    (7, 'Employee 7', 6, 2),
    (8, 'Employee 8', 7, 3),
    (9, 'Employee 9', 8, 4)
    ;

INSERT INTO Skills
    (id, name, is_star)
VALUES
    (1, 'Skill 1', FALSE),
    (2, 'Skill 2', TRUE),
    (3, 'Skill 3', FALSE),
    (4, 'Skill 4', TRUE),
    (5, 'Skill 5', FALSE)
    ;

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question