Ltree - how to sort nodes on parent node

From: cojack <xcojack(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Ltree - how to sort nodes on parent node
Date: 2010-04-19 07:23:09
Message-ID: hqh0cs$24bv$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
Im here because Oleg Bartunov invite me to this mailing list. Im searching
for help with ltree module, in todo list (in this module) is Better
documentation, since 2003 year, no one make something more for this. Whats
is the problem? With example from manual about ltree, we have some data in
our table, now add some row to table for Science node, and we will have
something like this:

id | path | sort
----+-----------------------------------------------+------
1 | Top | 1
2 | Top.Science | 1
3 | Top.Science.Astronomy | 1
4 | Top.Science.Astronomy.Astrophysics | 1
5 | Top.Science.Astronomy.Cosmology | 2
6 | Top.Hobbies | 2
7 | Top.Hobbies.Amateurs_Astronomy | 2
8 | Top.Collections | 3
9 | Top.Collections.Pictures | 1
10 | Top.Collections.Pictures.Astronomy | 1
11 | Top.Collections.Pictures.Astronomy.Stars | 1
12 | Top.Collections.Pictures.Astronomy.Galaxies | 2
13 | Top.Collections.Pictures.Astronomy.Astronauts | 3
15 | Top.Science.Programing | 3

Sort column, is added by my self, because Im trying to sort those columns,
but I don't know how. depesz from irc show me example how to sort this data
with creating ordering column in the fly:

WITH RECURSIVE rec AS (
SELECT *, btrim(to_char( sort, '0000000' )) AS ordering FROM tree WHERE
nlevel(path) = 1
UNION ALL
SELECT t2.*, t1.ordering || '/' || btrim(to_char( t2.sort, '0000000' )) AS
ordering FROM rec t1, tree t2 WHERE t1.path @> t2.path AND nlevel(t1.path) +
1 = nlevel(t2.path)
)
SELECT id, subpath(path, -1) AS title, nlevel(path) AS depth, sort FROM rec
ORDER BY ordering;

But I'm not sure it's the best way to sort this columns, so Im wrinting here
for help and some examples, or improve the ltree manual with more example.

--
Regards,
cojack.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yeb Havinga 2010-04-19 07:41:42 Re: Help with tracking!
Previous Message Craig Ringer 2010-04-19 05:52:44 Re: Help with tracking!