custom average window function failure

Поиск
Список
Период
Сортировка
От Seb
Тема custom average window function failure
Дата
Msg-id 87int2qkat.fsf@gmail.com
обсуждение исходный текст
Ответы Re: custom average window function failure  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: custom average window function failure  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hello,

Until I upgraded to PostgreSQL 9.6, a custom average function was
working well as a window function.  It's meant to average a composite
type:

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).';

The average function:

CREATE OR REPLACE FUNCTION public.angle_vectors_avg(angle_vectors_arr angle_vectors[])
  RETURNS vector AS
$BODY$
DECLARE
    x_avg double precision;
    y_avg double precision;
    magnitude double precision;
    angle_avg double precision;

BEGIN
    SELECT avg(x) INTO x_avg FROM unnest(angle_vectors_arr) irows;
    SELECT avg(y) INTO y_avg FROM unnest(angle_vectors_arr) irows;
    magnitude := sqrt((x_avg ^ 2.0) + (y_avg ^ 2.0));
    angle_avg := degrees(atan2(x_avg, y_avg));
    IF (angle_avg < 0 ) THEN
        angle_avg := angle_avg + 360.0;
    END IF;
    RETURN (angle_avg, magnitude);
END
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100;
COMMENT ON FUNCTION public.angle_vectors_avg(angle_vectors[]) IS 'This function computes the average angle from an
arrayof concatenated angle_vectors data type singletons.  It returns vector data type.'; 

And the aggregate:

CREATE AGGREGATE public.avg(angle_vectors) (
  SFUNC=array_append,
  STYPE=angle_vectors[],
  FINALFUNC=angle_vectors_avg
);

Query below used to work in PostgreSQL 9.5:

SELECT "time", avg((random(), random())::angle_vectors) over w
from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp, '5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");

but is now failing with the following message in 9.6:

ERROR:  input data type is not an array

********** Error **********

ERROR: input data type is not an array
SQL state: 42804

Any thoughts on what has changed that is leading to this failure?

--
Seb

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

Предыдущее
От: Stephen Davies
Дата:
Сообщение: Re: import_bytea function (resolved)
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: custom average window function failure