Message-ID: <4E537AFF.8050006@ulb.ac.be>
Date: Tue, 23 Aug 2011 12:03:43 +0200
From: Julien Cigar <jcigar@ulb.ac.be>
Organization: Belgian Biodiversity Platform
User-Agent: Mozilla/5.0 (X11; FreeBSD i386;
	rv:6.0) Gecko/20110817 Thunderbird/6.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: WITH RECURSIVE question
Content-Type: multipart/mixed; boundary="------------040201090805030504090601"

This is a multi-part message in MIME format.
--------------040201090805030504090601
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

--------------040201090805030504090601
Content-Type: text/x-vcard; charset=utf-8;
 name="jcigar.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="jcigar.vcf"

begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard


--------------040201090805030504090601--