Re: creating a new aggregate function

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