Lists: | pgsql-general |
---|
From: | Whit Armstrong <armstrong(dot)whit(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | example of aggregate function for product |
Date: | 2009-06-24 20:44:29 |
Message-ID: | 8ec76080906241344h4c97b996lcd320802cd150b1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I needed to write a product aggregate function, and just happened to
find this example in the nodes to the 8.0 manual:
Ansis <ansis_no_ AT inbox.lv>13 Jan 2006 16:41:05
An aggregate multiplication function, an analog of "sum" (the same
should be defined also for other numeric types):
CREATE OR REPLACE FUNCTION mul2(FLOAT,FLOAT)
RETURNS FLOAT AS '
DECLARE
a ALIAS FOR $1;
b ALIAS FOR $2;
BEGIN
RETURN a*b;
END;
' LANGUAGE plpgsql;
CREATE AGGREGATE mul (
sfunc = mul2,
basetype = FLOAT,
stype = FLOAT,
initcond = '1'
);
but that example looks pretty different than the ones found in the 8.3
manual (avg for instance):
CREATE AGGREGATE avg (float8)
(
sfunc = float8_accum,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0}'
);
and float8_accum is defined like this (in the postgres function list).
CREATE OR REPLACE FUNCTION float8_accum(double precision[], double precision)
RETURNS double precision[] AS
'float8_accum'
LANGUAGE 'internal' IMMUTABLE STRICT
COST 1;
ALTER FUNCTION float8_accum(double precision[], double precision)
OWNER TO postgres;
COMMENT ON FUNCTION float8_accum(double precision[], double precision)
IS 'aggregate transition function';
Are there any experts out there who have defined a product or
cumulative product function that abides best practices who would be
willing to share?
Thanks,
Whit
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Whit Armstrong <armstrong(dot)whit(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: example of aggregate function for product |
Date: | 2009-06-24 23:04:50 |
Message-ID: | 20090624230450.GG16227@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Whit Armstrong escribió:
> Ansis <ansis_no_ AT inbox.lv>13 Jan 2006 16:41:05
> An aggregate multiplication function, an analog of "sum" (the same
> should be defined also for other numeric types):
>
> CREATE OR REPLACE FUNCTION mul2(FLOAT,FLOAT)
> RETURNS FLOAT AS '
> DECLARE
> a ALIAS FOR $1;
> b ALIAS FOR $2;
[...]
> but that example looks pretty different than the ones found in the 8.3
> manual (avg for instance):
The main difference is that the 8.3 docs example piggybacks on C
language functions that you must compile and install separately, whereas
the comment function uses a plpgsql function and is self-contained.
Other than that (and the fact that the second one is for averages not
multiplication), both examples are technically identical ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Whit Armstrong *EXTERN*" <armstrong(dot)whit(at)gmail(dot)com>, "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: example of aggregate function for product |
Date: | 2009-06-25 07:46:57 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C202FF6683@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Whit Armstrong wrote:
> I needed to write a product aggregate function, and just happened to
> find this example in the nodes to the 8.0 manual:
>
[...]
>
> but that example looks pretty different than the ones found in the 8.3
> manual (avg for instance):
>
[...]
>
> Are there any experts out there who have defined a product or
> cumulative product function that abides best practices who would be
> willing to share?
I don't know what "best practices" are, but the following works
fine on PostgreSQL 8.3 and 8.4:
CREATE AGGREGATE mul(double precision) (SFUNC=float8mul, STYPE=double precision, INITCOND=1);
Yours,
Laurenz Albe