To: pgsql-sql@postgresql.org
From: Sebastian P. Luque <spluque@gmail.com>
Subject: Re: creating a new aggregate function
Date: Mon, 03 Mar 2014 18:57:17 -0600
Organization: Church of Emacs
Lines: 79
Message-ID: <87ha7eu6fm.fsf@net82.ceos.umanitoba.ca>
References: <87iorvurrd.fsf@net82.ceos.umanitoba.ca>
 <1393868159887-5794419.post@n5.nabble.com>
 <87wqgbt3kf.fsf@net82.ceos.umanitoba.ca>
 <1393881121206-5794448.post@n5.nabble.com>
 <87lhwqu8q6.fsf@net82.ceos.umanitoba.ca>
 <20952.1393892275@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
Cancel-Lock: sha1:7agvttAJybsfrX5PZ6pdBPzDup0=
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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$
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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql