multiply(num) aggregate function in postgresql

Question

This could be incredibly simple by the documentation is quite on it. Is there a way to aggregate columns via multiplication operator in postgresql. I know i can do count(column) or sum(column), but is there a multiply(column) or product(column) function that i can use. If not, any ideas how to achieve it.

I'm using postgres 9.1

regards, Hassan

Answer 1

Sure, just define an aggregate over the base multiplication function. E.g. for bigint:

CREATE AGGREGATE mul(bigint) ( SFUNC = int8mul, STYPE=bigint );

Example:

regress=> SELECT mul(x) FROM generate_series(1,5) x;
 mul 
-----
 120
(1 row)

See CREATE AGGREGATE

Answer 2

Here is a version that works for all numerical data types:

CREATE FUNCTION mul_sfunc(anyelement, anyelement) RETURNS anyelement
   LANGUAGE sql AS 'SELECT $1 * coalesce($2, 1)';

CREATE AGGREGATE mul(anyelement) (
   STYPE = anyelement,
   INITCOND = 1,
   SFUNC = mul_sfunc,
   COMBINEFUNC = mul_sfunc,
   PARALLEL = SAFE
);