agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedcreating a new aggregate function
7+ messages / 4 participants
[nested] [flat]
* creating a new aggregate function
@ 2014-03-03 17:16 Seb <spluque@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Seb @ 2014-03-03 17:16 UTC (permalink / raw)
To: pgsql-sql
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.
Cheers,
--
Seb
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: creating a new aggregate function
@ 2014-03-03 17:35 David Johnston <polobo@yahoo.com>
parent: Seb <spluque@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Johnston @ 2014-03-03 17:35 UTC (permalink / raw)
To: pgsql-sql
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.
Note the use of plpgsql in your function is probably undesirable since you
are not actually using any procedural logic; an SQL language function is
better since it gives the system more optimization options.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/creating-a-new-aggregate-function-tp5794414p5794419.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: creating a new aggregate function
@ 2014-03-03 20:44 Seb <spluque@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Seb @ 2014-03-03 20:44 UTC (permalink / raw)
To: 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$
DECLARE
x numeric;
y numeric;
BEGIN
x := 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: creating a new aggregate function
@ 2014-03-03 21:12 David Johnston <polobo@yahoo.com>
parent: Seb <spluque@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Johnston @ 2014-03-03 21:12 UTC (permalink / raw)
To: pgsql-sql
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.
Dave
--
View this message in context: http://postgresql.1045698.n5.nabble.com/creating-a-new-aggregate-function-tp5794414p5794448.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: creating a new aggregate function
@ 2014-03-04 00:07 Seb <spluque@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Seb @ 2014-03-04 00:07 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: creating a new aggregate function
@ 2014-03-04 00:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seb <spluque@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2014-03-04 00:17 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
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[] ?
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: creating a new aggregate function
@ 2014-03-04 00:57 Sebastian P. Luque <spluque@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Sebastian P. Luque @ 2014-03-04 00:57 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-03-04 00:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-03 17:16 creating a new aggregate function Seb <spluque@gmail.com>
2014-03-03 17:35 ` David Johnston <polobo@yahoo.com>
2014-03-03 20:44 ` Seb <spluque@gmail.com>
2014-03-03 21:12 ` David Johnston <polobo@yahoo.com>
2014-03-04 00:07 ` Seb <spluque@gmail.com>
2014-03-04 00:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-04 00:57 ` Sebastian P. Luque <spluque@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox