Richard Huxton wrote:
On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote:Standard Postgresql aggregate functions don't need sorted data, but my function needs. Look at the data:
>Hi
>I created my own aggregate function working as max(sum(value))
>It adds positive and negative values and finds maximum of this sum.
>To work properly this function needs data to be sorted.
I'm not sure that an aggregate function should require data to be sorted
before processing. Could you show details of your function - there may be a
way to rewrite it to handle unsorted data.
<value> <sum>
3 3
-2 1
6 7 *** max_sum=7
-3 4
2 6
For example, if you inverse your data, you have:
<value> <sum>
2 2
-3 -1
6 5
-2 3
3 6 *** max_sum=6
As you see, data order is very important in this aggregate.
The function is very easy:
CREATE OR REPLACE FUNCTION maxsum_counter(_int4, int4) RETURNS _int4 AS '
DECLARE
old_val ALIAS for $1;
curr_val ALIAS for $2;
new_max int4;
new_sum int4;
BEGIN
new_sum=old_val[1]+curr_val;
if new_sum > old_val[1] then
new_max=new_sum;
else
new_max=old_val[2];
end if;
return ''{'' || new_sum || '','' || new_max || ''}'';
END;
' LANGUAGE 'plpgsql';
OR REPLACE FUNCTION extract_maxsum(_int4) RETURNS "int4" AS '
DECLARE
old_val ALIAS for $1;
BEGIN
return old_val[2];
END;
' LANGUAGE 'plpgsql';
DROP AGGREGATE maxsum int4;
CREATE AGGREGATE maxsum(
BASETYPE = int4,
SFUNC = maxsum_counter,
STYPE = _int4,
FINALFUNC = extract_maxsum,
INITCOND = '{0,0}');
>select
> maxsum(value)
>from some_table
> order by some_field
>
>doesn't work:
>ERROR: Attribute some_table.some_field must be GROUPed or used in an
>aggregate function
The "order by" isn't necessarily handled before calculating maxsum() anyway.
Nice point. Anyway it doesn't matter, because it isn't handled at all.
Tomasz Myrta ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
