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.
<<attachment: jcigar.vcf>>
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
