On Mon, 03 Mar 2014 19:17:55 -0500,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Seb <spluque@gmail.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$
BEGINRETURN 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$
DECLARExyrows angle_vector;x_avg numeric;y_avg numeric;magnitude numeric;angle_avg numeric;
BEGINxyrows := 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
