On Mon, May 4, 2009 at 10:42 AM, A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Thanks Andreas, your solution works quite well, much appreciated. Allan -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
