Re: Strict min and max aggregate functions
| От | Kim Rose Carlsen |
|---|---|
| Тема | Re: Strict min and max aggregate functions |
| Дата | |
| Msg-id | AM4PR0501MB2610192BBF43D85E8FD847F6C7B20@AM4PR0501MB2610.eurprd05.prod.outlook.com обсуждение исходный текст |
| Ответ на | Strict min and max aggregate functions (Jeff Janes <jeff.janes@gmail.com>) |
| Список | pgsql-general |
> 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
> );
>
It seems like this should be possible to do in something more close to O(log n). But I'm not sure how to fix the semantics with aggregates.
SELECT max(<column>) FROM <table>;
SELECT true FROM <table> WHERE <column> IS NULL LIMIT 1;
Both these queries can be resolved with a index lookup (if one is available).
В списке pgsql-general по дате отправления: