2016-11-19 22:12 GMT+01:00 Jeff Janes <jeff.ja...@gmail.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 >
