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?
