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
