PostgreSQL 8.2 and above has this pretty neat feature of allowing you to define aggregate functions
that take more than one column as an input. First we'll start off with a rather pointless but easy to relate to example and then we'll follow up with something
a bit more interesting.
For more examples of creating aggregates in PostgreSQL, check out our other articles:
We have all seen aggregate functions that take one column as argument, but what about ones that take
two or three or four or more. Why would you ever want to do this? The first example we will show is one that
puts me to sleep, but demonstrates the point.
We'll call it the prodsum. What it does is this.
prodsum(a,b) = a1*b1 + a2*b2 + a3*b3 ....an*bn
Any astitute observer will observe that this is just a lame use of something as powerful as multicolumn aggregates
because you can easily simulate it with the below. Its only nice because it allows us to validate our result.
prodsum(a,b) = SUM(a*b)
You would form the aggregate product sum by doing this