Re: creating a new aggregate function

On Mon, 03 Mar 2014 19:17:55 -0500,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Seb <spluque(at)gmail(dot)com> writes:
>> Thanks for that suggestion. It seemed as if array_agg would allow me
>> to define a new aggregate for avg as follows:

>> CREATE AGGREGATE avg (angle_vector) ( sfunc=array_agg,
>> stype=anyarray, finalfunc=angle_vector_avg );

> That's not going to work, for exactly this reason:

>> ERROR: cannot determine transition data type DETAIL: An aggregate
>> using a polymorphic transition type must have at least one
>> polymorphic argument.

> I see no reason to use a polymorphic type here anyway ... why not just
> declare the transition data type as angle_vector[] ?

OK, then it seems as if I must create custom sfunc *and* finalfunc:

-- sfunc
CREATE OR REPLACE FUNCTION angle_vector_accum(angle_vectors angle_vector[], angle_vector angle_vector)
RETURNS angle_vector[] AS
$BODY$
BEGIN
RETURN array_append(angle_vectors, angle_vector)::angle_vector[];
END
$BODY$
LANGUAGE plpgsql STABLE;

-- finalfunc
CREATE OR REPLACE FUNCTION angle_vector_avg(angle_vector_arr angle_vector[])
RETURNS record AS
$BODY$
DECLARE
xyrows angle_vector;
x_avg numeric;
y_avg numeric;
magnitude numeric;
angle_avg numeric;

BEGIN
xyrows := unnest(angle_vector_arr);
x_avg := avg(xyrows.x);
y_avg := avg(xyrows.y);
magnitude := sqrt((x_avg ^ 2.0) + (y_avg ^ 2.0));
angle_avg := degrees(atan2(x_avg, y_avg));
IF (angle_avg < 0.0) THEN
angle_avg := angle_avg + 360;
END IF;
RETURN (angle_avg, magnitude);
END
$BODY$
LANGUAGE plpgsql STABLE;

CREATE AGGREGATE avg (angle_vector)
(
sfunc=angle_vector_accum,
stype=angle_vector[],
finalfunc=angle_vector_avg
);

But calling the aggregate with this statement:

SELECT avg(decompose_angle(angle, magnitude))
FROM (VALUES (10, 1), (350, 2), (200, 3)) AS a (angle, magnitude);

fails with:

ERROR: query "SELECT unnest(angle_vector_arr)" returned more than one row
CONTEXT: PL/pgSQL function angle_vector_avg(angle_vector[]) line 10 at assignment

But looks like I'm getting close!

Thanks,

--
Seb