To: Richard Huxton <dev@archonet.com>
Cc: Kai Hessing <kai.hessing@hobsons.de>, pgsql-sql@postgresql.org
Subject: Re: Advanced SELECT 
In-reply-to: <421D8EA6.7020107@archonet.com> 
References: <383fk1F5hq3hhU1@individual.net> <421D8EA6.7020107@archonet.com>
Comments: In-reply-to Richard Huxton <dev@archonet.com>
	message dated "Thu, 24 Feb 2005 08:21:58 +0000"
Date: Thu, 24 Feb 2005 10:32:16 -0500
Message-ID: <25016.1109259136@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Richard Huxton <dev@archonet.com> writes:
> Search the mailing-list archives for "custom aggregate concat" and 
> you'll quickly find an example of how to write your own custom aggregate 
> (like SUM()).

> Warning - I don't think you can guarantee the order of elements in the 
> aggregated sectors.

In recent PG versions you can.  For example,

	select key1, custom_aggregate(detail_field) from
	(select key1, detail_field from my_table order by key1, key2) ss
	group by key1 order by key1;

The detail_field values will be fed to the aggregate in order by key2
within each key1 group.  This is one of the examples that motivated
allowing ORDER BY in subselects, even though it's outside the SQL spec.

[ experiments... ]  This works reliably in 7.4 and up.  Before that,
the optimizer didn't make the connection between the sort ordering of
the inner query and that needed by the outer, so it would repeat the
sort step using only key1 and very possibly destroy the key2 ordering.

			regards, tom lane