Re: creating a new aggregate function - Mailing list pgsql-sql

On Mon, 3 Mar 2014 09:35:59 -0800 (PST),
David Johnston <polobo@yahoo.com> wrote:

> Sebastian P. Luque wrote
>> Hi,

>> I'm trying to implement an aggregate function to calculate the
>> average angle from one or more angles and corresponding magnitudes.
>> So my first step is to design a function that decomposes the angles
>> and magnitudes and returns the corresponding x and y vectors, and the
>> following works does this:

>> ---<--------------------cut
here---------------start-------------------> ---
>> CREATE OR REPLACE FUNCTION decompose_angle(IN angle numeric, IN
>> magnitude numeric, OUT x numeric, OUT y numeric) RETURNS record AS
>> $BODY$ BEGIN x := sin(radians(angle)) * magnitude; y :=
>> cos(radians(angle)) * magnitude; END; $BODY$ LANGUAGE plpgsql STABLE
>> COST 100; ALTER FUNCTION decompose_angle(numeric, numeric) OWNER TO
>> sluque; COMMENT ON FUNCTION decompose_angle(numeric, numeric) IS
>> 'Decompose an angle and magnitude into x and y vectors.';
>> ---<--------------------cut
here---------------end---------------------> ---

>> Before moving on to writing the full aggregate, I'd appreciate any
>> suggestions to understand how to go about writing an aggregate for
>> the above, that would return the average x and y vectors.

> I would suggest you design custom types that incorporate the
> angle,magnitude-pair and the x,y-pair and write your functions to
> operate using those types.

> The documentation for CREATE AGGREGATE is fairly detailed and can be
> summarized as: 1) Do something for each input row - you maintain state
> internally 2) Do something after the last row has been processed -
> using the state from #1

> http://www.postgresql.org/docs/9.3/interactive/sql-createaggregate.html

> What you do in those two steps depends fully on the algorithm you need
> which is beyond my immediate knowledge.

Thanks for your feedback.  I already have the algorithm defined in
another language, so that is not a problem.

I created a type to hold the decomposed x,y (decomposed vectors):

CREATE TYPE angle_vectors AS  (x numeric,   y numeric);

And now I can write:

CREATE OR REPLACE FUNCTION decompose_angle(angle numeric, magnitude numeric) RETURNS angle_vectors AS
$BODY$
DECLAREx numeric;y numeric;
BEGINx := sin(radians(angle)) * magnitude;y := cos(radians(angle)) * magnitude;RETURN (x, y);
END;
$BODY$ LANGUAGE plpgsql STABLE COST 100;

[I'll look into using simple SQL for this, as you suggest]

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?

Thanks,

-- 
Seb