Обсуждение: WITH RECURSIVE question

Поиск
Список
Период
Сортировка

WITH RECURSIVE question

От
Julien Cigar
Дата:
Hello,

I have a classic parent -> child relation (id, container_id) and I would
like to find the full hierarchy for a child, something like

   A
  / \
B   C
|
D

given D I want {A,B,C}

WITH RECURSIVE hierarchy(level, container_id, titles, containers) AS
(SELECT 1 AS level, container_id, ARRAY[title::text] AS titles,
         ARRAY[container_id] AS containers
  FROM content
  WHERE id=984
UNION ALL
  SELECT hierarchy.level + 1, c.container_id,
         array_prepend(c.title::text, titles),
         array_prepend(c.container_id, containers)
  FROM content c
  JOIN hierarchy ON hierarchy.container_id = c.id
) SELECT titles, containers
   FROM hierarchy
   ORDER BY level DESC LIMIT 1;

which give me something like:

         titles        |  containers
----------------------+--------------
  {Home,Templates,IAS} | {NULL,1,983}
(1 row)

This is exactly what I want, but I wondered if there are better ways to
do it? Is it scalable?

(The final idea is to put that in a trigger, so that it will be executed
only when a new row is added, or when the row is moved from a container
to another one ..)

Thanks,
Julien

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

Вложения

Re: WITH RECURSIVE question

От
Julien Cigar
Дата:
mmh I made a little mistake: it was {A,B} of course, not {A,B,C}

On 08/23/2011 12:03, Julien Cigar wrote:
> given D I want {A,B,C}


--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

Вложения