From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Scott Gerhardt <scott@g-it.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate query for multiple records
Date: Sat, 28 Aug 2004 12:19:37 -0400
Message-ID: <20037.1093709977@sss.pgh.pa.us> (raw)
In-Reply-To: <87eklrlkwh.fsf@stark.xeocode.com>
References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
<200408270948.00680.josh@agliodbs.com>
<AF64E983-F851-11D8-A763-000393801C60@g-it.ca>
<200408271159.26445.josh@agliodbs.com>
<87eklrlkwh.fsf@stark.xeocode.com>
Greg Stark <gsstark@mit.edu> writes:
> [ nice example snipped ]
> ... Also, you'll have to change it to use reals.
That part, at least, can be worked around as of 7.4: use polymorphic
functions. You can declare the functions and aggregate as working on
anyelement/anyarray, and then they will automatically work on any
datatype that has a + operator.
regression=# create or replace function first_6_accum (anyarray,anyelement) returns anyarray
regression-# language sql immutable as 'select case when array_upper($1,1)>=6 then $1 else $1||$2 end';
CREATE FUNCTION
regression=# create function sum_6(anyarray) returns anyelement immutable language sql as 'select $1[1]+$1[2]+$1[3]+$1[4]+$1[5]+$1[6]';
CREATE FUNCTION
regression=# create aggregate sum_first_6 (basetype=anyelement, sfunc=first_6_accum, stype=anyarray,initcond='{}',finalfunc=sum_6);
CREATE AGGREGATE
regression=# select sum_first_6(i) from (select i from (select 1 as i union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8) as x order by i desc) as x;
sum_first_6
-------------
33
(1 row)
regression=# select sum_first_6(i) from (select i from (select 1.1 as i union select 2 union select 3 union select 4 union select 5 union select 6 union select 7.7 union select 8) as x order by i desc) as x;
sum_first_6
-------------
33.7
(1 row)
regression=#
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gsstark@mit.edu, josh@agliodbs.com, scott@g-it.ca
Subject: Re: Aggregate query for multiple records
In-Reply-To: <20037.1093709977@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox