Re: Calculating product from rows - (aggregate product )

In response to Allan Kamau :
> Hi
>
> I would like to calculate a product of a field's values of a relation,
> this function may multiply each value and give the result as a single
> float number.
>
> For example:
>
>
> CREATE table imaginary(id INTEGER NOT NULL, some_field FLOAT
> NULL,primary key(id));
>
> INSERT INTO imarginary(1,0.333);INSERT INTO imarginary(2,0.667);INSERT
> INTO imarginary(3,0.4);
>
>
> SELECT prod(some_field) FROM imarginary;
>
>
> would give 0.0888444 (which is 0.333*0.667*0.4)
>
>
> Is there an already existing function that does this.

No, you need a own aggregate function, but it is easy:

test=# CREATE FUNCTION multiply_aggregate(float,float) RETURNS float AS
' select $1 * $2; ' language sql IMMUTABLE STRICT; CREATE AGGREGATE
multiply (basetype=float, sfunc=multiply_aggregate, stype=float,
initcond=1 ) ;
CREATE FUNCTION
CREATE AGGREGATE
test=*# create table float_test(a float);
CREATE TABLE
test=*# copy float_test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 0.333
>> 0.4
>> 0.8
>> \.
test=*# select multiply(a) from float_test;
multiply
----------
0.10656
(1 row)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net