Herewith a patch to implement agg(foo ORDER BY bar) with or without DISTINCT, etc. No artificial restrictions are imposed on what syntactical combinations are allowed. However, ORDER BY is not allowed with aggregates used as window functions (as per the existing restriction on DISTINCT).
Included are regression tests but not docs yet, those will follow shortly in a separate patch (to save having to keep redoing the code patch like last time). Caveat: as discussed earlier, this patch changes the behaviour of array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs were unconditionally skipped; now, they are treated just like DISTINCT or GROUP BY normally do. The previous restriction of agg(DISTINCT ...) to single-argument aggregates is removed. However, there is still a separate code path for aggregates that use DISTINCT or ORDER BY with only one input column, for performance reasons. If a non-default ordering operator is used in combination with DISTINCT, then the notion of "equality" used for the DISTINCT comparisons is the one that belongs to the ordering, rather than the default. -- Andrew.
aorder-20091113.patch.gz
Description: agg order by patch
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers