From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: terry@ashtonwoodshomes.com, "Postgresql Sql Group (E-mail)" <pgsql-sql@postgresql.org>
Subject: Re: Return relation table data in a single value CSV
Date: Tue, 17 Feb 2004 18:33:54 -0500
Message-ID: <18788.1077060834@sss.pgh.pa.us> (raw)
In-Reply-To: <200402172104.38307.dev@archonet.com>
References: <007701c3f591$5acc3020$2766f30a@development.greatgulfhomes.com>
<200402172104.38307.dev@archonet.com>
Richard Huxton <dev@archonet.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Return relation table data in a single value CSV
In-Reply-To: <18788.1077060834@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox