Как написать рекурсивный запрос на обновление значения?

Question

[dlysenko1311]

как написать рекурсионный запрос, который который добавляет столбец значением которого будет первый уровень иерархии, т.е. для значение 9 уровня будет значение соответствующего 1 уровня иерархии?

Пример того, что я делаю

Есть такая таблица:

CREATE TABLE geo (
    id int not null primary key, 
    parent_id int references geo(id),  
    name varchar(1000)
);

INSERT INTO geo 
(id, parent_id, name) 
VALUES 
(1, null, 'Планета Земля'),
(2, 1, 'Континент Евразия'),
(3, 1, 'Континент Северная Америка'),
(4, 2, 'Европа'),
(5, 4, 'Россия'),
(6, 4, 'Германия'),
(7, 5, 'Москва'),
(8, 5, 'Санкт-Петербург'),
(9, 6, 'Берлин');

Выбираем всё, что относится к Европе:

WITH RECURSIVE r AS (
   SELECT id, parent_id, name
   FROM geo
   WHERE parent_id = 4

   UNION

   SELECT geo.id, geo.parent_id, geo.name
   FROM geo
      JOIN r
          ON geo.parent_id = r.id
)

И вот что мы получили:

SELECT * FROM r;

 id | parent_id |      name       
----+-----------+-----------------
  5 |         4 | Россия
  6 |         4 | Германия
  7 |         5 | Москва
  8 |         5 | Санкт-Петербург
  9 |         6 | Берлин

И мне нужно из этой же таблицы сделать такую:

SELECT * FROM r;

 id | parent_id |      name           firs_level
----+-----------+-----------------
  5 |         4 | Россия                        4
  6 |         4 | Германия                        4
  7 |         5 | Москва                        4
  8 |         5 | Санкт-Петербург          4
  9 |         6 | Берлин                        4

То есть, у меня есть другая большая тбалица, мне нужно добавить столбец, который будет содержать в себе значение первого уровня иерархии.
пример, который я привел наверное не очень подходит здесь, но в примере который я привел я знаю, что все строки имеют связь с европой так как делал выборку по ней.
Как сделать что то то подобное? То есть, добавить столбец, значением которого будет первый уровень иерархии
НО, если брать самую первыую таблицу, таблицу, то понятно, что значение столбца first_level везде будет 1
Но в моей релаьно таблице, будет несколько слоев

Comments

[mayton2019]

Шутник. Планету указал. Давай добей до конца. Солнечная система. Рукав Ориона. Ланниакея. и т.д.

Answer 1

[Дмитрий]

Сорряныч не правильно прочитал - не уровень иерархии а id первого уровня.
Тыц

WITH RECURSIVE r AS (
   SELECT id, parent_id, name, 1 as level, parent_id as first_level
   FROM geo
   WHERE parent_id = 4

   UNION

   SELECT geo.id, geo.parent_id, geo.name, (r.level+1), r.first_level
   FROM geo
      JOIN r
          ON geo.parent_id = r.id
)
SELECT * FROM r;