"David E. Wheeler" <da...@kineticode.com> writes: > On Sep 1, 2010, at 10:52 AM, Thom Brown wrote: > >>>> ould appreciate the recipe for removing the NULLs. >>> >>> WHERE clause :P >> >> There may be cases where that's undesirable, such as there being more >> than one aggregate in the SELECT list, or the column being grouped on >> needing to return rows regardless as to whether there's NULLs in the >> column being targeted by array_agg() or not. > > Exactly the issue I ran into: > > SELECT name AS distribution, > array_agg( > CASE relstatus WHEN 'stable' > THEN version > ELSE NULL > END ORDER BY version) AS stable, > array_agg( > CASE relstatus > WHEN 'testing' > THEN version > ELSE NULL > END ORDER BY version) AS testing > FROM distributions > GROUP BY name;
What about adding WHERE support to aggregates, adding to the ORDER BY capability they already have? SELECT array_agg(version WHERE relstatus = 'stable' ORDER BY version) The current way to do that is using a subquery and unnest() and where clause there, but that's not a good way to avoid to process stored data in the aggregate / in the query. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
