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 array of 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
