User-defined aggregate functions with multiple input columns in PostgreSQL

Question

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.

Answer 1

See this: How to create multi-column aggregates, which is available since PostgreSQL 8.2

As for multiple state variables, as Jack said, you can use an array as the state variable.

Answer 2

From your link: "avg (average) is a more complex example of an aggregate. It requires two pieces of running state: the sum of the inputs and the count of the number of inputs. The final result is obtained by dividing these quantities. Average is typically implemented by using a two-element array as the state value."

How about doing something like that?