From: | Matteo Beccati <php(at)beccati(dot)com> |
---|---|
To: | Dimitri Fontaine <dfontaine(at)hi-media(dot)com> |
Cc: | Aidan Van Dyk <aidan(at)highrise(dot)ca>, Magnus Hagander <magnus(at)hagander(dot)net>, Dave Page <dpage(at)pgadmin(dot)org>, Abhijit Menon-Sen <ams(at)toroid(dot)org>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: mailing list archiver chewing patches |
Date: | 2010-01-14 15:06:33 |
Message-ID: | 4B4F32F9.1040309@beccati.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-www |
Il 14/01/2010 15:47, Dimitri Fontaine ha scritto:
> Matteo Beccati<php(at)beccati(dot)com> writes:
>> WITH RECURSIVE t (mailbox, uid, date, subject, sender, has_attachments,
>> parent_uid, idx, depth) AS (
>> SELECT mailbox, uid, date, subject, sender, has_attachments, parent_uid,
>> uid::text, 1
>> FROM arc_messages
>> WHERE parent_uid IS NULL AND mailbox = 15
>> UNION ALL
>> SELECT a.mailbox, a.uid, a.date, a.subject, a.sender, a.has_attachments,
>> a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1
>> FROM t JOIN arc_messages a USING (mailbox)
>> WHERE t.uid = a.parent_uid
>> ) SELECT * FROM t ORDER BY idx
>>
>> Any improvements to sorting are welcome :)
>
> What I'd like would be to have it sorted by activity, showing first the
> thread which received the later messages. I'm yet to play with CTE and
> window function myself so without a database example to play with I
> won't come up with a nice query, but I guess a more educated reader will
> solve this without a sweat, as it looks easier than sudoku-solving,
> which has been done already :)
Eheh, that was my first try as well. CTEs look very nice even though I'm
not yet very comfortable with the syntax. Anyway both for date and
thread indexes sort is the other way around, with newer posts/threads at
the bottom. Again I'll give it a try as soon as I find time to work
again on it.
Cheers
--
Matteo Beccati
Development & Consulting - http://www.beccati.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2010-01-14 15:09:43 | Re: mailing list archiver chewing patches |
Previous Message | Pavel Stehule | 2010-01-14 14:53:29 | Re: [PL/PgSQL] EXECUTE...USING enhancement proposal |
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2010-01-14 15:09:43 | Re: mailing list archiver chewing patches |
Previous Message | Dimitri Fontaine | 2010-01-14 14:47:02 | Re: mailing list archiver chewing patches |