Re: Strict min and max aggregate functions

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

> On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 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?
>>
>
> I can. Would there be an advantage?
>

PLpgSQL uses prepared statements - the expressions should be evaluated
faster. In this case there are not possible SQL inlining.

>
> you can use composite type instead array too.
>>
>
> I tried a composite type of (flag int, value anyelement) but you can't use
> anyelement in a composite type. So the aggregate function couldn't be
> polymorphic. Or, that was my conclusion after making a few attempts. Maybe
> I need to give on polymorphism if I want to get performance?
>
> Cheers,
>
> Jeff
>