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
CREATE OR REPLACE FUNCTION prod_state(prev numeric, e1 numeric, e2 numeric)
RETURNS numeric AS
$$
SELECT COALESCE($1,0) + COALESCE($2*$3,0);
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE prodsum(numeric, numeric)(
SFUNC=prod_state,
STYPE=numeric,
INITCOND=0
);
Now to validate that it is doing the right thing..
SELECT k,
prodsum(i,j + k) As prodsum ,
sum(i*(j + k)) As regsum
FROM generate_series(1,4) As i
CROSS JOIN generate_series(1,2) As j
CROSS JOIN generate_series(0,1) As k
GROUP BY k
ORDER BY k;
k | prodsum | regsum
---+---------+--------
0 | 27 | 27
1 | 45 | 45
Now this example is a little trickier to simulate without an aggregate function that takes more than one argument, so we won't bother
funky_agg(a,b) = a10*b1 + a21*b2 ...ann-1*bn
--Funky agg CREATE OR REPLACE FUNCTION funky_state(prev numeric[2], e1 numeric, e2 numeric) RETURNS numeric[2] AS $$ SELECT ARRAY[CASE WHEN $1[2] = 0 THEN 1*$3 ELSE $1[1] + POWER($2,$1[2])*$3 END, $1[2] + 1] ; $$ LANGUAGE 'sql' IMMUTABLE; CREATE OR REPLACE FUNCTION funky_final(last numeric[2]) RETURNS numeric AS $$ SELECT $1[1]; $$ LANGUAGE 'sql' IMMUTABLE; CREATE AGGREGATE funky_agg(numeric, numeric) ( SFUNC=funky_state, STYPE=numeric[2], FINALFUNC=funky_final, INITCOND = '{1,0}' ); --The foos SELECT i,j,k FROM generate_series(0,2) As i CROSS JOIN generate_series(1,2) As j CROSS JOIN generate_series(0,1) As k ORDER BY k,i,j;i | j | k ---+---+--- 0 | 1 | 0 0 | 2 | 0 1 | 1 | 0 1 | 2 | 0 2 | 1 | 0 2 | 2 | 0 0 | 1 | 1 0 | 2 | 1 1 | 1 | 1 1 | 2 | 1 2 | 1 | 1 2 | 2 | 1 (12 rows)SELECT k , funky_agg(foo.i,foo.j + foo.k) As what FROM (SELECT i,j,k FROM generate_series(0,2) As i CROSS JOIN generate_series(1,2) As j CROSS JOIN generate_series(0,1) As k ORDER BY k,i,j) As foo GROUP BY k ORDER BY k;k | what ---+---------------------- 0 | 84.0000000000000000 1 | 135.0000000000000000
Checking our math by expanding by hand:
k = 0: SELECT 1*1 + POWER(0,1)*2 + POWER(1,2)*1 + POWER(1,3)*2
+ POWER(2,4)*1 + POWER(2,5)*2 ; --84
k = 1: SELECT 1*(1+1) + POWER(0,1)*(2 + 1) + POWER(1,2)*(1 + 1)
+ POWER(1,3)*(2+1) + POWER(2,4)*(1+1) + POWER(2,5)*(2+1) ; -- 135
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
CREATE OR REPLACE FUNCTION prod_state(prev numeric, e1 numeric, e2 numeric)
RETURNS numeric AS
$$
SELECT COALESCE($1,0) + COALESCE($2*$3,0);
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE prodsum(numeric, numeric)(
SFUNC=prod_state,
STYPE=numeric,
INITCOND=0
);
Now to validate that it is doing the right thing..
SELECT k,
prodsum(i,j + k) As prodsum ,
sum(i*(j + k)) As regsum
FROM generate_series(1,4) As i
CROSS JOIN generate_series(1,2) As j
CROSS JOIN generate_series(0,1) As k
GROUP BY k
ORDER BY k;
k | prodsum | regsum
---+---------+--------
0 | 27 | 27
1 | 45 | 45
Now this example is a little trickier to simulate without an aggregate function that takes more than one argument, so we won't bother
funky_agg(a,b) = a10*b1 + a21*b2 ...ann-1*bn
--Funky agg CREATE OR REPLACE FUNCTION funky_state(prev numeric[2], e1 numeric, e2 numeric) RETURNS numeric[2] AS $$ SELECT ARRAY[CASE WHEN $1[2] = 0 THEN 1*$3 ELSE $1[1] + POWER($2,$1[2])*$3 END, $1[2] + 1] ; $$ LANGUAGE 'sql' IMMUTABLE; CREATE OR REPLACE FUNCTION funky_final(last numeric[2]) RETURNS numeric AS $$ SELECT $1[1]; $$ LANGUAGE 'sql' IMMUTABLE; CREATE AGGREGATE funky_agg(numeric, numeric) ( SFUNC=funky_state, STYPE=numeric[2], FINALFUNC=funky_final, INITCOND = '{1,0}' ); --The foos SELECT i,j,k FROM generate_series(0,2) As i CROSS JOIN generate_series(1,2) As j CROSS JOIN generate_series(0,1) As k ORDER BY k,i,j;i | j | k ---+---+--- 0 | 1 | 0 0 | 2 | 0 1 | 1 | 0 1 | 2 | 0 2 | 1 | 0 2 | 2 | 0 0 | 1 | 1 0 | 2 | 1 1 | 1 | 1 1 | 2 | 1 2 | 1 | 1 2 | 2 | 1 (12 rows)SELECT k , funky_agg(foo.i,foo.j + foo.k) As what FROM (SELECT i,j,k FROM generate_series(0,2) As i CROSS JOIN generate_series(1,2) As j CROSS JOIN generate_series(0,1) As k ORDER BY k,i,j) As foo GROUP BY k ORDER BY k;k | what ---+---------------------- 0 | 84.0000000000000000 1 | 135.0000000000000000
Checking our math by expanding by hand:
k = 0: SELECT 1*1 + POWER(0,1)*2 + POWER(1,2)*1 + POWER(1,3)*2
+ POWER(2,4)*1 + POWER(2,5)*2 ; --84
k = 1: SELECT 1*(1+1) + POWER(0,1)*(2 + 1) + POWER(1,2)*(1 + 1)
+ POWER(1,3)*(2+1) + POWER(2,4)*(1+1) + POWER(2,5)*(2+1) ; -- 135