Re: checking for NULLS in aggregate

On Wed, Jun 24, 2015 at 12:54 PM, Seb <spluque(at)gmail(dot)com> wrote:

> Hello,
>
> I've defined a function to calculate standard deviation of angular
> values:
>
> CREATE AGGREGATE public.stddev(angle_vectors) (
> SFUNC=array_append,
> STYPE=angle_vectors[],
> FINALFUNC=angle_vectors_stddev_yamartino
> );
>
> The type angle_vectors is simply an array:
>
> CREATE TYPE public.angle_vectors AS
> (x double precision,
> y double precision);
> COMMENT ON TYPE public.angle_vectors
> IS 'This type holds the x (sine) and y (cosine) components of angle(s).';
>
> How can I protect this function so that NULL is returned whenever *all*
> input rows are NULL?
>
> Thanks for any feedback,
>

​Going from memory here...​

​You don't "protect" anything. angle_vectors_stddev_yamartino needs to
check whether its input array only contains null and, if so, return null.

AFAIK there is no built-in way to check for only-null since "null =
ALL(...)" doesn't work and you cannot say "IS NULL ALL(...)"

In a simple case you could do something like:

SELECT array_to_string(ARRAY[null,null]::integer[],'') = ''

​Not sure how that translates when using a composite type...and there is a
difference between a non-null type with null components and a nulled type.

David J.​