Re: Multi-parameter aggregates.

On Mon, Nov 21, 2005 at 11:03:22AM -0500, Berend Tober wrote:
> I'm interested in defining a covariance aggregate function. (As a
> refresher, remember that covariance is a little bit like variance, but
> is between two variables:
>
> cov(X,Y) = <XY> - <X><Y>,
>
> where the angular brackets in this case denote taking the averag.
> Variance is a special case when X and Y are the same.)
>
> But the whole user-defined aggregate thing is tough to get a handle on.
> I'm not even sure if the direction I'm heading in below will actually
> work, but as far as I got, I'm stuck on not knowing how to define a
> aggregate that takes more that one variable as its argument, so its use
> in SQL would look like, e.g.,
>
> SELECT company, COVAR(year, sales) FROM annual_sales GROUP BY company;

I think aggregates must take a single value, so the above won't
work as written. However, in PostgreSQL 8.0 or later you could
define the aggregate's base type to be a composite type and do
something like

SELECT company, COVAR(ROW(year, sales)) FROM annual_sales GROUP BY company;

You'd create the aggregate like this:

CREATE TYPE covar_state AS (...);
CREATE TYPE xypair AS (x numeric, y numeric);

CREATE FUNCTION covar_accum(covar_state, xypair) RETURNS covar_state AS ...
CREATE FUNCTION covar_final(covar_state) RETURNS numeric AS ...

CREATE AGGREGATE covar (
BASETYPE = xypair,
SFUNC = covar_accum,
FINALFUNC = covar_final,
STYPE = covar_state,
INITCOND = '(...)'
);

--
Michael Fuhr