Re: checking for NULLS in aggregate

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: checking for NULLS in aggregate
Дата
Msg-id CAKFQuwY7+vOvRioJy56QMy11ae20Sg7eYeA-6F-qHLfxadaDgA@mail.gmail.com
обсуждение исходный текст
Ответ на checking for NULLS in aggregate  (Seb <spluque@gmail.com>)
Список pgsql-general
On Wed, Jun 24, 2015 at 12:54 PM, Seb <spluque@gmail.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.​

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Include.d and warnings
Следующее
От: "Sheena, Prabhjot"
Дата:
Сообщение: pgbouncer issue