Re: LTREE extension and "order by"

hi, thank you, but there is error:

ERROR: cannot cast type ltree to text
LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):...

Ivan

On 21 July 2011 17:25, Carla <cgourofino(at)hotmail(dot)com> wrote:
> Try it:
> select * from comments where article_id = 2 order by
> string_to_array(path::text,'.')::integer[];
>
> 2011/7/21 Ivan Polak <ivan(dot)polak(at)f4s(dot)sk>
>>
>> Hi, thank you for your answer, please can You send me complete select
>> command how to convert ltree column to integer[] and use it to order
>> by.
>>
>> thanks
>>
>> Ivan
>>
>> 2011/7/21 pasman pasmański <pasman(dot)p(at)gmail(dot)com>:
>> > Hi.
>> >
>> > You should convert path to integer[].
>> >
>> > 2011/7/20, Ivan Polak <ivan(dot)polak(at)f4s(dot)sk>:
>> >> Hi,
>> >>
>> >> in postgreSQL (with LTREE extension) database I have the following
>> >> table "comments":
>> >>
>> >> id BIGINT /* id */
>> >> article_id BIGINT /*article-id */
>> >> parent_id BIGINT
>> >> comment TEXT
>> >> path LTREE
>> >> level INTEGER /* level */
>> >>
>> >> with the following rows:
>> >>
>> >> id article_id comment parent_id path level
>> >> 1 1 aaaa 1 1
>> >> 2 1 bbbb 1 1.2 2
>> >> 3 1 cccc 2 1.2.3 3
>> >> 4 1 dddd 2 1.4 2
>> >> 5 1 eeee 4 1.4.5 3
>> >> 6 1 ffff 6 1
>> >> 7 1 gggg 6 6.7 2
>> >> 8 1 hhhh 6 6.8 2
>> >> 9 1 iiii 9 1
>> >> 10 1 jjjj 10 1
>> >> 11 1 kkkk 5 1.4.5.11 4
>> >>
>> >> and I need to select complete tree (with correct order of comments).
>> >>
>> >> SELECT * from comments where article_id = 2 order by <???>
>> >>
>> >> when I used:
>> >>
>> >> SELECT * from comments where article_id = 2 order by path
>> >>
>> >> the result is:
>> >>
>> >> id comment path
>> >> 1 aaaa 1
>> >> 2 bbbb 1.2
>> >> 3 cccc 1.2.3
>> >> 4 dddd 1.4
>> >> 5 eeee 1.4.5
>> >> 11 kkkk 1.4.5.11
>> >> 10 jjjj 10
>> >> 6 ffff 6
>> >> 7 gggg 6.7
>> >> 8 hhhh 6.8
>> >> 9 iiii 9
>> >>
>> >> BUT, it is wrong, because comment with id = 10 is after comment with
>> >> id=11
>> >>
>> >> (i know, this is correct, because ordering by column PATH [as TEXT],
>> >> and 10 is 'after' 1.4.5.11)
>> >>
>> >> , but I need :
>> >>
>> >> id comment path
>> >> 1 aaaa 1
>> >> 2 bbbb 1.2
>> >> 3 cccc 1.2.3
>> >> 4 dddd 1.4
>> >> 5 eeee 1.4.5
>> >> 11 kkkk 1.4.5.11
>> >> 6 ffff 6
>> >> 7 gggg 6.7
>> >> 8 hhhh 6.8
>> >> 9 iiii 9
>> >> 10 jjjj 10
>> >>
>> >> thanks
>> >>
>> >> Ivan
>> >>
>> >> --
>> >> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-sql
>> >>
>> >
>> >
>> > --
>> > ------------
>> > pasman
>> >
>> > --
>> > Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-sql
>> >
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>