Обсуждение: user defined aggregate with multiple arguments
I have a need to create some statistical functions in PostgreSQL that will take multiple "column" arguments and return multiple values. A good example would be a least squares fit of a line through a set of (x,y) points. In this case, I would want the function to accept two "columns" of data (i.e. x data and y data) as arguments and return two parameters: the slope and intercept of a line. The slope is given by [sum(x^2)*sum(y) - sum(x)*sum*x*y)] / [M*sum(x^2) -(sum(x))^2] It's not *that* complicated, but it's not that easy to remember either. And if you're already doing a lot of work to tease out the x and y points from various tables, it simplifies your query if you can do a function call. I can't tell from what have read about user defined functions and user defined aggregates whether this kind of function is possible. Bill Eaton
> I have a need to create some statistical functions in PostgreSQL that will take multiple > "column" arguments and return multiple values. > > A good example would be a least squares fit of a line through a set of (x,y) points. > In this case, I would want the function to accept two "columns" of data (i.e. x data and > y data) as arguments and return two parameters: the slope and intercept of a line. > > The slope is given by [sum(x^2)*sum(y) - sum(x)*sum*x*y)] / [M*sum(x^2) -(sum(x))^2] > > It's not *that* complicated, but it's not that easy to remember either. And if you're already > doing a lot of work to tease out the x and y points from various tables, it simplifies your query > if you can do a function call. > > I can't tell from what have read about user defined functions and user defined aggregates whether > this kind of function is possible. > > Bill Eaton I just notice that multiple inputs to aggregates are allowed in the upcoming 8.2 release. This gives me a great incentive to play with the beta and upgrade from 8.0. One question remains: how about multiple outputs? Can I have a ROW as a return value -- i.e. something like SLOPE and INTERCEPT in the original example? -Bill Eaton
On Wed, 11 Oct 2006, Bill Eaton wrote: > > I just notice that multiple inputs to aggregates are allowed in the upcoming > 8.2 release. This gives me a great incentive to play with the beta and > upgrade from 8.0. > > One question remains: how about multiple outputs? Can I have a ROW as a > return value -- i.e. something like SLOPE and INTERCEPT in the original > example? > First, you don't have to write these functions by yourself. The functions for least squares fit are defined in the standart, and they will be also in 8.2. http://momjian.us/main/writings/pgsql/sgml/functions-aggregate.html But, in any way, if you want your aggregate to return several values, you can always return them as the elements in the array. Regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
> On Wed, 11 Oct 2006, Bill Eaton wrote: >> >> I just notice that multiple inputs to aggregates are allowed in the >> upcoming 8.2 release. This gives me a great incentive to play with >> the beta and upgrade from 8.0. >> >> One question remains: how about multiple outputs? Can I have a ROW as >> a return value -- i.e. something like SLOPE and INTERCEPT in the >> original example? >> > Sergey E. Koposov wrote: > First, you don't have to write these functions by yourself. The > functions for least squares fit are defined in the standart, and they > will be also in 8.2. > http://momjian.us/main/writings/pgsql/sgml/functions-aggregate.html > > But, in any way, if you want your aggregate to return several values, > you can always return them as the elements in the array. Actually, I was wanting to do higher order polynomial fits. But it's cool that there are some new functions. So you suggest I use an array as a return type. Hmm. Never did much with arrays before. So that means a "row" or "setof" is not an option? -Bill