Strict min and max aggregate functions

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Strict min and max aggregate functions
Date: 2016-11-19 21:12:58
Message-ID: CAMkU=1z8WhMwO-90rT+4_s6T7+J6yyr9E-xRcwmquddqivVUZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
);

Cheers,

Jeff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-11-19 21:18:18 Re: Database migration to RDS issues permissions
Previous Message Tom Lane 2016-11-19 20:57:19 Re: Partial update on an postgres upsert violates constraint