Обсуждение: Creating Aggregate functions in PLpgSQL
Is it possible to create aggregate functions using pl/pgsql? If not possible in plpgsql, is there any other way to create these types of functions? If anyone could point to the correct documentation I would be most appreciative. Regards, Richard Broersma Jr.
On 12/11/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
Yes, the sfunc and ffunc can be functions written in plpgsql.
Yes, but I don't know the details (sorry for the near worthless answer)
http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html is for 8.3 where there were some changes to the number of arguments an aggregate could take. I believe it's also more descriptive documentation.
In general, create a type to hold your state, a sfunc and a ffunc then create your aggregate pointing at those types and functions.
create type my_state as (
my_sum bigint,
my_count bigint
);
create or replace function my_avg_sfunc(state my_state, nextvalue bigint) returns my_state as $$
begin
state.my_sum := state.my_sum + nextvalue;
state.my_count := state.my_count + 1;
end; $$ language plpgsql;
create or replace function my_avg_ffunc(state my_state) returns float as $$
begin
return state.my_sum::float / state.my_count::float ;
end; $$ language plpgsql;
create aggregate my_avg(bigint) (
stype = my_state,
sfunc = my_avg_sfunc,
finalfunc = my_avg_ffunc,
initcond = '(0, 0)'
);
of course for things like average you wouldn't need a custom type...
Is it possible to create aggregate functions using pl/pgsql?
Yes, the sfunc and ffunc can be functions written in plpgsql.
If not possible in plpgsql, is there any other way to create these types of functions?
Yes, but I don't know the details (sorry for the near worthless answer)
If anyone could point to the correct documentation I would be most appreciative.
http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html is for 8.3 where there were some changes to the number of arguments an aggregate could take. I believe it's also more descriptive documentation.
In general, create a type to hold your state, a sfunc and a ffunc then create your aggregate pointing at those types and functions.
create type my_state as (
my_sum bigint,
my_count bigint
);
create or replace function my_avg_sfunc(state my_state, nextvalue bigint) returns my_state as $$
begin
state.my_sum := state.my_sum + nextvalue;
state.my_count := state.my_count + 1;
end; $$ language plpgsql;
begin
return state.my_sum::float / state.my_count::float ;
end; $$ language plpgsql;
create aggregate my_avg(bigint) (
stype = my_state,
sfunc = my_avg_sfunc,
finalfunc = my_avg_ffunc,
initcond = '(0, 0)'
);
of course for things like average you wouldn't need a custom type...
--- On Tue, 12/11/07, Matthew Dennis <mdennis@merfer.net> wrote: > http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html > is for > 8.3 where there were some changes to the number of > arguments an aggregate > could take. I believe it's also more descriptive > documentation. > > of course for things like average you wouldn't need a > custom type... Thanks for the information it is very useful. The reason that I ask, is that I am trying to develop a way to calculate theaverage power factor(scalar) of a Motor Control Center (among other things). The Electrical Engineers that are askingfor the report introduced me to a rather complicated formula to implement in standard SQL. The best way that I can describe the problem get the PF is finding the angle between Xcomp/hypotenuse after having used Pythagoreantheorem to find the hypotenuse after having summing of multiple 2 coordinate vectors(the EEs use the term phasers)for each MCC cubical. It seems they have need for quite a few other little aggregate functions that they would like me to make if I can get thisone done first. Anyway thank for the push in the right direction! Regards, Richard Broersma Jr. Anyway
"Matthew Dennis" <mdennis@merfer.net> writes: > http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html is for > 8.3 where there were some changes to the number of arguments an aggregate > could take. I believe it's also more descriptive documentation. Also see the overview at http://www.postgresql.org/docs/8.3/static/xaggr.html regards, tom lane
--- On Tue, 12/11/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Also see the overview at > http://www.postgresql.org/docs/8.3/static/xaggr.html Thanks Tom!
MG>obligatory toppost > > http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html > > is for > > 8.3 where there were some changes to the number of > > arguments an aggregate > > could take. I believe it's also more descriptive > > documentation. > > > > of course for things like average you wouldn't need a > > custom type... > > Thanks for the information it is very useful. The reason that I ask, is that I am trying to develop a way to calculate the average power factor(scalar) of a Motor Control Center (among other things). The Electrical Engineers that are asking for the report introduced me to a rather complicated formula to implement in standard SQL. > > The best way that I can describe the problem get the PF Is this Picofarad or PetaFarad? is finding the angle between Xcomp MG>what is Xcomp? /hypotenuse after having used Pythagorean theorem to find the hypotenuse after having summing of multiple 2 coordinate vectors(the EEs use the term phasers) MG>set on stun? for each MCC cubical. MG>assume Motor Control Cubical? > > It seems they have need for quite a few other little aggregate functions that they would like me to make if I can get this one done first. > > Anyway thank for the push in the right direction! > > Regards, > Richard Broersma Jr. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
--- On Wed, 12/12/07, Martin Gainty <mgainty@hotmail.com> wrote: > MG>what is Xcomp? I really meant x component = effective component of power as opposed to the reactive component of power. > MG>set on stun? :o) I guess a better way to describe the problem is that total power has both a real and imaginary component. So in thisway it can be liked to a complex numeric value. > MG>assume Motor Control Cubical? correct. There is a power distribution hierarchy that they would like to analyze. 1) the average pF of all motor in a Motor Control Center (MCC). 2) the average pF of all MCCs that are fed from a Load Center (LC). 3) the average pF of all LC that are fed from the facility main feed. The pF is measured between 0 and 1. 1 being purely effective power and 0 being purge reactive power. The EEs want to identifyMCCs or LCs that may need Capacitor banks to help offset the effective of reactive power inherent in the inductiveload of motors. This is a perfect problem for a custom aggregate. Regards, Richard Broersma Jr.
Em Wednesday 12 December 2007 11:57:48 Richard Broersma Jr escreveu: > > There is a power distribution hierarchy that they would like to analyze. > > 1) the average pF of all motor in a Motor Control Center (MCC). > 2) the average pF of all MCCs that are fed from a Load Center (LC). > 3) the average pF of all LC that are fed from the facility main feed. > > The pF is measured between 0 and 1. 1 being purely effective power and 0 > being purge reactive power. The EEs want to identify MCCs or LCs that may > need Capacitor banks to help offset the effective of reactive power > inherent in the inductive load of motors. Actually pF is measured from -1 to 1. There is a problem of what type of reactive power (inductive or capacitive) you have on your facility. Since you are working with motors you are worried with the inductive type, but it would be nice if you could get ready before they tell you that they want doing some pF correction on the facility and you need to have that counted as well. -- Jorge Godoy <jgodoy@gmail.com>
--- On Thu, 12/13/07, Jorge Godoy <jgodoy@gmail.com> wrote: > Actually pF is measured from -1 to 1. > > they tell you that they want doing some pF correction on > the facility and you > need to have that counted as well. Thanks for the correct, and good point. :-) Regards, Richard Broersma Jr.