HI, thanks, thanks Carla, your solution is OK :-) (i`m using PostgreSQL 8.2, so Pavel is right).
Ivan On 21 July 2011 18:28, Carla <cgourof...@hotmail.com> wrote: > 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.po...@f4s.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 <cgourof...@hotmail.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.po...@f4s.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 <pasma...@gmail.com>: >> >> > Hi. >> >> > >> >> > You should convert path to integer[]. >> >> > >> >> > 2011/7/20, Ivan Polak <ivan.po...@f4s.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@postgresql.org) >> >> >> To make changes to your subscription: >> >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> > >> >> > >> >> > -- >> >> > ------------ >> >> > pasman >> >> > >> >> > -- >> >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> > To make changes to your subscription: >> >> > http://www.postgresql.org/mailpref/pgsql-sql >> >> > >> >> >> >> -- >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> > >> > >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
