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.
