Re: custom average window function failure

On 10/08/2016 08:21 PM, Seb wrote:
> 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?

Not sure. When I tried using the above(on 9.5) it failed during the
CREATE OR REPLACE FUNCTION public.angle_vectors_avg stage with:

ERROR:  type "vector" does not exist


So where is that coming from in your setup?

--
Adrian Klaver
adrian.klaver@aklaver.com