2016-11-20 20:18 GMT+01:00 Jeff Janes <jeff.ja...@gmail.com>: > On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> >> >> 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? >> > > 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 >
