Обсуждение: creating a new aggregate function
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) RETURNSrecord AS $BODY$ BEGINx := 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
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.
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
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.
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$ DECLARExyrows angle_vector;x_avg numeric;y_avg numeric;magnitude numeric;angle_avg numeric; BEGINxyrows := 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
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
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$ BEGINRETURN 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$ DECLARExyrows angle_vector;x_avg numeric;y_avg numeric;magnitude numeric;angle_avg numeric; BEGINxyrows := 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