Richard Huxton wrote:
> On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote:
>
> >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.
Standard Postgresql aggregate functions don't need sorted data, but my
function needs. Look at the 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
