Multi-parameter aggregates.

[Berend Tober <btober@xxxxxxxxxxxxxxxx>]

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;

Here is what I tried, and I'm wondering if the team here can help me 
make this work (or tell me that the whole approach makes no sense, 
etc.). All the DDL executes without syntactical errors until the last 
function definition, and the problem is with the "  BASETYPE=numeric" 
line, i.e., "ERROR:  AggregateCreate: function 
covariance_accum(numeric[], numeric) does not exist"

CREATE TYPE public._covariance AS
   (n integer, x numeric, y numeric, xy numeric);


CREATE OR REPLACE FUNCTION public.covariance_accum(_covariance, numeric, 
numeric)
   RETURNS _covariance AS '
   BEGIN
   _covariance.n := _covariance.n+1;
   _covariance.x := _covariance.x+$2;
   _covariance.y := _covariance.x+$3;
   _covariance.xy:= _covariance.xy+($1*$2);
   END;
   'LANGUAGE 'plpgsql' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION public.covariance_accum(_covariance, numeric, 
numeric) TO public;
COMMENT ON FUNCTION public.covariance_accum(_covariance, numeric, 
numeric) IS 'covariance aggregate transition function';


-- Need to include a check for N equal zero data points

CREATE OR REPLACE FUNCTION public.numeric_covariance(_covariance)
   RETURNS numeric AS '
   BEGIN
   (_covariance.xy/_covariance.n) - 
(_covariance.x/_covariance.n)*(_covariance.y/_covariance.n);
   END;
   'LANGUAGE 'plpgsql' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION public.numeric_covariance(_covariance) TO public;
COMMENT ON FUNCTION public.numeric_covariance(_covariance) IS 
'covariance aggregate final function';



CREATE AGGREGATE public.covariance(
 BASETYPE=numeric,
 SFUNC=covariance_accum,
 STYPE=numeric[],
 FINALFUNC=numeric_covariance,
 INITCOND='{0,0,0,0}'
);


/*
--I also tried this:

CREATE AGGREGATE covariance(
 BASETYPE='numeric, numeric',
 SFUNC=covariance_accum,
 STYPE=numeric[],
 FINALFUNC=numeric_covariance,
 INITCOND='{0,0,0,0}'
);

-- to no avail.
*/

Regards,
Berend


begin:vcard
fn:Berend Tober
n:Tober;Berend
org:Seaworthy Systems, Inc.
adr:;;22 Main Street;Centerbrook;CT;06409;USA
email;internet:btober@xxxxxxxxxxxxxxxx
tel;work:860-767-9061
url:http://www.seaworthysys.com
version:2.1
end:vcard