agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: creating a new aggregate function
Date: Mon, 03 Mar 2014 18:07:45 -0600
Message-ID: <87lhwqu8q6.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (7+ messages) latest in thread
Message-ID: <87lhwqu8q6.fsf@net82.ceos.umanitoba.ca>
Permalink: ../87lhwqu8q6.fsf@net82.ceos.umanitoba.ca/
Also on: postgresql.org/message-id/87lhwqu8q6.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: <87lhwqu8q6.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