Re: [SQL] Need to return one field for multiple Rows

2011-07-21 Thread Ozer, Pam
Never Mind Ifigured it out. I just need to use agg_array From: Ozer, Pam Sent: Thursday, July 21, 2011 4:09 PM To: pgsql-sql@postgresql.org Subject: Need to return one field for multiple Rows I have the following query Select PubArticleId, PubArticlePageXML from PubarticlePageXml x

[SQL] Need to return one field for multiple Rows

2011-07-21 Thread Ozer, Pam
I have the following query Select PubArticleId, PubArticlePageXML from PubarticlePageXml x Inner Join Pubarticlepage p on x.pubarticlepageid=p.pubarticlepageid where pubarticleid=188321 Which Returns PubArticleId(Integer)PubArticlePageXML(Text) 188321 fdfaldkjf 1

Re: [SQL] LTREE extension and "order by"

2011-07-21 Thread Ivan Polak
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 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

Re: [SQL] LTREE extension and "order by"

2011-07-21 Thread Pavel Stehule
2011/7/21 Carla : > 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[]); > this does not work in older versions you can use http://www

Re: [SQL] LTREE extension and "order by"

2011-07-21 Thread Carla
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 > hi, thank you, but there is error: > > ERROR: cannot cast type

Re: [SQL] LTREE extension and "order by"

2011-07-21 Thread Ivan Polak
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 wrote: > Try it: > select * from comments where article_id = 2 order by > string_to_array(path::text,'.')::integer[

Re: [SQL] LTREE extension and "order by"

2011-07-21 Thread Carla
Try it: select * from comments where article_id = 2 order by string_to_array(path::text,'.')::integer[]; 2011/7/21 Ivan Polak > 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 >

Re: [SQL] LTREE extension and "order by"

2011-07-21 Thread Ivan Polak
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 : > Hi. > > You should convert path to integer[]. > > 2011/7/20, Ivan Polak : >> Hi, >> >> in postgreSQL (with LTR