Re: LTREE extension and "order by"

Hmm, I'm using PostgreSQL 8.4 and it worked.
Try to use the function ltree2text instead of ::text.
select * from comments where article_id = 2 order by
cast(string_to_array(ltree2text(path),'.') as integer[]);

2011/7/21 Ivan Polak <ivan(dot)polak(at)f4s(dot)sk>

> 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
> >>
> >
> >
>
> --
> 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
>
>