Ltree - how to sort nodes on parent node

Поиск
Список
Период
Сортировка
Искать
От
cojack
Тема
Ltree - how to sort nodes on parent node
Дата
Msg-id
hqh0cs$24bv$1@news.hub.org
Список
Дерево обсуждения
Re: Ltree - how to sort nodes on parent node Harald Fuchs <hari.fuchs@gmail.com>
Re: Ltree - how to sort nodes on parent node Alban Hertroys <dalroi@solfertje.student.utwente.nl>
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.
В списке pgsql-general по дате отправления
От: Rene Schickbauer
Дата:
Сообщение: Virtual table with pl/perl?
От: Koichi Suzuki
Дата:
FAQ