Re: Strict min and max aggregate functions

2016-11-19 22:12 GMT+01:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> I need "strict" MIN and MAX aggregate functions, meaning they return NULL
> upon any NULL input, and behave like the built-in aggregates if none of the
> input values are NULL.
>
> This doesn't seem like an outlandish thing to want, and I'm surprised I
> can't find other discussion of it. Perhaps because none of the words here
> are very effective as search terms as they are so individually common.
>
> I've hit upon a solution that works, but it is both ugly and slow (about
> 50 fold slower than the built-ins; for my current purpose this is not a big
> problem but I would love it to be faster if that could be done easily).
>
> So here is my approach. Any suggestions to improve it? Or are there
> better canned solutions I've failed to find?
>
>
> -- If no values have been delivered to the aggregate, the internal state
> is the
> -- NULL array. If a null values has been delivered, the internal status
> is an
> -- array with one element, which is NULL. Otherwise, it is an array with
> one element,
> -- the least/greatest seen so far.
>
> CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement )
> RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
> SELECT CASE
> WHEN $1 IS NULL THEN ARRAY[$2]
> WHEN $1[1] IS NULL THEN $1
> WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve
> type
> ELSE ARRAY[least($1[1],$2)] END ;
> $$;
>
>
> CREATE OR REPLACE FUNCTION strict_min_final (anyarray)
> RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
> SELECT CASE WHEN $1 IS NULL THEN NULL ELSE $1[1] END ;
> $$;
>
> CREATE AGGREGATE strict_min (x anyelement) (
> sfunc = strict_min_agg,
> stype = anyarray,
> finalfunc = strict_min_final
> );
>

can you use plpgsql instead sql?

you can use composite type instead array too.

Regards

Pavel

>
>
> Cheers,
>
> Jeff
>