agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Sebastian P. Luque <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: creating a new aggregate function
Date: Mon, 03 Mar 2014 18:57:17 -0600
Message-ID: <87ha7eu6fm.fsf@net82.ceos.umanitoba.ca> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (7+ messages)

Message-ID: <87ha7eu6fm.fsf@net82.ceos.umanitoba.ca>
Permalink:  ../87ha7eu6fm.fsf@net82.ceos.umanitoba.ca/
Also on:    postgresql.org/message-id/87ha7eu6fm.fsf@net82.ceos.umanitoba.ca

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: spluque@gmail.com
  Subject: Re: creating a new aggregate function
  In-Reply-To: <87ha7eu6fm.fsf@net82.ceos.umanitoba.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox