From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Strict min and max aggregate functions |
Date: | 2016-11-20 19:18:05 |
Message-ID: | CAMkU=1w8m7+a2spQ1aE5y7frcr3Og5QH+=GG=K926Y8oaWYGnQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2016-11-20 19:29:11 | Re: Strict min and max aggregate functions |
Previous Message | Tom Lane | 2016-11-20 16:39:39 | Re: How to change order sort of table in HashJoin |