> Alban Hertroys wrote: > > > Aha, looks like you want to sort each tree level by some user-specified > order. > > You should realise that ltree was contributed before Postgres supported > (recursive) CTE's. If you're using ltree in combination with recursive > CTE's you're doing twice the work that you need to do - ltree was created > as a means to make recursive queries possible in the first place. > > I think you have basically two ways to go about this: > > 1). The way you're doing this in your new examples should work, although > I'd probably make the ordering numbers part of the category names and > split those off when I read them. For example: > 27 | 1|Top > 28 | 1|Top.1|Science > 29 | 1|Top.2|Hobby > 30 | 1|Top.3|Colors > 31 | 1|Top.1|Science.1|Physics > 32 | 1|Top.1|Science.2|Chemistry > 33 | 1|Top.1|Science.3|Biology > 34 | 1|Top.1|Science.4|History > 35 | 1|Top.2|Hobby.1|Fishing > 36 | 1|Top.2|Hobby.2|Football > 37 | 1|Top.3|Colors.1|Black > 38 | 1|Top.3|Colors.2|Red > 39 | 1|Top.3|Colors.3|Blue > 40 | 1|Top.1|Science.5|Archeology > 41 | 1|Top.2|Hobby.3|Swimming > 42 | 1|Top.3|Colors.4|Gray > 43 | 1|Top.3|Colors.5|Purple > 44 | 1|Top.3|Colors.6|Brown > 45 | 1|Top.2|Hobby.4|Climbing > > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:737,4bcd773910411833268189! > > > My and your first example doesn't work fine at all, why? Becouse when we add more thank 10 sub nodes in some node, the 10 node will not be after 9, but after 1 before 2, and this is not good idea to set sort in path. I think the best idea for this will be create other column, with also ltree data type and stored inside a sort/ordering data. Like: 1 1.1 1.1.1 1.1.2 1.1.3 And while selected it from table, just cast it to int. I'll check this and his performance after I return from work. I am not interested about recursive queries, i think this kill ltree idea. -- Regards, cojack. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
