I am trying to create user-defined aggregate functions that use multiple columns as their input, and output a single column.
For example, to calculate a weighted average, we might use two columns called num_samples and quantity, with a query like this:
SELECT sum(num_samples * quantity) / sum(num_samples) AS weighted_avg FROM table;
However, the functions I want to define are quite complex (e.g. weighted standard deviation) and are used many times. I'd like to be define my own aggregate functions so that they can be easily used in select queries. For example, if I wanted to find the weighted average and total sum, I'd use a query like this:
SELECT weighted_avg(num_samples, quantity), sum(quantity)
However, from the documentation it looks like user-defined aggregates are only allowed a single state variable, but this example would require two state variables: one for the running total of quantity and one for the running total of num_samples.
Is it possible to achieve what I want with user-defined aggregate functions, or is there a better way? I'm using PostgreSQL 8.3.
