pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWITH RECURSIVE question
2+ messages / 1 participants
[nested] [flat]
* WITH RECURSIVE question
@ 2011-08-23 10:03 Julien Cigar <jcigar@ulb.ac.be>
2011-08-23 14:39 ` Re: WITH RECURSIVE question Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 1 reply; 2+ messages in thread
From: Julien Cigar @ 2011-08-23 10:03 UTC (permalink / raw)
To: pgsql-sql
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.
Attachments:
[text/x-vcard] jcigar.vcf (290B, ../../4E537AFF.8050006@ulb.ac.be/2-jcigar.vcf)
download | inline:
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: WITH RECURSIVE question
2011-08-23 10:03 WITH RECURSIVE question Julien Cigar <jcigar@ulb.ac.be>
@ 2011-08-23 14:39 ` Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 0 replies; 2+ messages in thread
From: Julien Cigar @ 2011-08-23 14:39 UTC (permalink / raw)
To: pgsql-sql
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.
Attachments:
[text/x-vcard] jcigar.vcf (304B, ../../4E53BBAD.8000807@ulb.ac.be/2-jcigar.vcf)
download | inline:
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-08-23 14:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-23 10:03 WITH RECURSIVE question Julien Cigar <jcigar@ulb.ac.be>
2011-08-23 14:39 ` Julien Cigar <jcigar@ulb.ac.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox