Re: Calculating product from rows - (aggregate product ) [solution found, thanks Andreas]

On Mon, May 4, 2009 at 10:42 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Andreas, your solution works quite well, much appreciated.

Allan