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 
In-reply-to: <200402172104.38307.dev@archonet.com> 
References: <007701c3f591$5acc3020$2766f30a@development.greatgulfhomes.com>
	<200402172104.38307.dev@archonet.com>
Comments: In-reply-to Richard Huxton <dev@archonet.com>
	message dated "Tue, 17 Feb 2004 21:04:38 +0000"
Date: Tue, 17 Feb 2004 18:33:54 -0500
Message-ID: <18788.1077060834@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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