To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: Re: creating a new aggregate function
Date: Mon, 03 Mar 2014 18:07:45 -0600
Organization: Church of Emacs
Lines: 92
Message-ID: <87lhwqu8q6.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>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
Cancel-Lock: sha1:CogU7mJyAQ33HVZ8Nq6aSE8ZxDc=
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On Mon, 3 Mar 2014 13:12:01 -0800 (PST),
David Johnston <polobo@yahoo.com> wrote:

> Sebastian P. Luque wrote
>> If I wanted to create an aggregate that also returns an angle_vectors
>> data type (with the average x and y components), I would need to
>> write a state transition function (sfunc for 'CREATE AGGREGATE') that
>> essentially sums every row and keeps track of the count of elements.
>> In turn, this requires defining a new data type for the output of
>> this state transition function, and finally write the final function
>> (ffunc) that takes this output and divides the sum of each component
>> (x, y) and divides it by the number of rows processed.  This seems
>> very complicated, and it would help to look at how avg (for instance)
>> was implemented.  I could not find examples in the documentation
>> showing how state transition and final functions are designed.  Any
>> tips?

> "avg" is defined in 'C' so not sure you'd find it of help...

> It may be easier, and sufficient, to use "array_agg" to build of an
> array of some kind and then process the array since it sounds like you
> cannot easily define a state-transition function that does what it
> says, transitions from one "minimal" state to another "minimal" state.
> For instance, the average function maintains a running count and a sum
> of all inputs so that no matter how many inputs are encountered at any
> point in the processing the only in-memory data are the last count/sum
> pair and the current value to be added to the sum (while incrementing
> the count).  If your algorithm does not facilitate this kind of
> transition function logic then whether you incorporate the array into
> your own custom aggregate or use the native "array_agg" facility
> probably makes little difference.

> Mostly speaking from theory here so you may wish to take this with a
> grain of sand and maybe waits for others more experienced to chime in.
> Either way hopefully it helps at least somewhat.

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
);

where angle_vector is the composite type as defined in my previous
email, and angle_vector_avg is a function taking anyarray, which would
use unnest() to allow access to the x,y components and carry out the
computations:

---<--------------------cut here---------------start------------------->---
CREATE OR REPLACE FUNCTION angle_vector_avg(angle_vector_arr anyarray)
  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
  COST 100;
---<--------------------cut here---------------end--------------------->---

Unfortunately, 'CREATE AGGREGATE' in this case returns:

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

********** Error **********

ERROR: cannot determine transition data type
SQL state: 42P13
Detail: An aggregate using a polymorphic transition type must have at least one polymorphic argument.


-- 
Seb


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