From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | terry(at)ashtonwoodshomes(dot)com, "Postgresql Sql Group (E-mail)" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Return relation table data in a single value CSV |
Date: | 2004-02-17 23:33:54 |
Message-ID: | 18788.1077060834@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Richard Huxton <dev(at)archonet(dot)com> writes:
> 2. Write a custom aggregate function (like sum()) to do the concatenation.
> This is easy to do, but the order your ABC get processed in is undefined.
Actually, as of 7.4 it is possible to control the order of inputs to a
custom aggregate. You do something like this:
SELECT foo, myagg(bar) FROM
(SELECT foo, bar FROM table ORDER BY foo, baz) AS ss
GROUP BY foo
The inner sub-select must order by the columns that the outer will group
on; it can then order by additional columns that determine the sort
order within each group. Here, myagg() will see its input ordered by
increasing values of baz.
Before 7.4 this method didn't work because the planner was too stupid to
avoid re-sorting the subquery output. You could only make it work in
cases where you weren't doing grouping ...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-02-17 23:40:49 | Re: CHAR(n) always trims trailing spaces in 7.4 |
Previous Message | elein | 2004-02-17 23:17:28 | Re: CHAR(n) always trims trailing spaces in 7.4 |