Re: array_agg() NULL Handling

2010/9/1 Thom Brown <thom(at)linux(dot)com>:
> On 1 September 2010 18:47, David Fetter <david(at)fetter(dot)org> wrote:
>> On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote:
>>> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:
>>>
>>> > Docs is wrong :) I like current implementation.  You can remove a
>>> > NULLs from aggregation very simply, but different direction isn't
>>> > possible
>>>
>>> Would 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.

Then you can eliminate NULLs with simple function

CREATE OR REPLACE FUNCTION remove_null(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT x FROM unnest($1) g(x) WHERE x IS NOT NULL)
$$ LANGUAGE sql;
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>