From: | Joel Reed <joelreed(at)openarc(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | string_agg hanging? |
Date: | 2011-02-21 20:06:58 |
Message-ID: | 4D62C5E2.3080904@openarc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hoping someone will be kind enough to share how to write a query that
uses 9.0's string_agg with a subselect, something like...
select
m.id,m.subject,m.from_address,m.date_sent,m.size,string_agg((select
address as to_address from recipient r inner join message_recipient mr
on r.id=mr.recipient_id and mr.message_id=m.id and
mr.recipient_type='To'), ', ') from message m, recipient r group by
m.id, m.subject, m.from_address, m.date_sent, m.size limit 20;
Unforunately, that just seems to hang. So I'm using...
select
m.id,m.subject,m.from_address,m.date_sent,m.size,array_to_string(ARRAY(select
r.address from recipient r inner join message_recipient mr on
r.id=mr.recipient_id and message_id=m.id and mr.recipient_type='To'),
',') as to_addresses from message m, recipient r limit 20;
Which returns in under 4 seconds. Am I doing something wrong with the
string_agg query to make it hang?
Thanks in advance - I greatly appreciate any help you can offer.
jr
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2011-02-21 20:32:34 | Re: disable triggers using psql |
Previous Message | Geoffrey Myers | 2011-02-21 18:53:58 | Re: disable triggers using psql |