Postgres Pro
Downloads
Home   >   mailing lists

Re: Strict min and max aggregate functions - Mailing list pgsql-general

From Kim Rose Carlsen
Subject Re: Strict min and max aggregate functions
Date
Msg-id AM4PR0501MB2610192BBF43D85E8FD847F6C7B20@AM4PR0501MB2610.eurprd05.prod.outlook.com
Whole thread Raw
In response to Strict min and max aggregate functions  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
Tree view

> 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 by date:

Previous
From: Vincent Elschot
Date:
Subject: Re: Trim performance on 9.5
Next
From: Man
Date:
Subject: Re: [HACKERS] How to change order sort of table in HashJoin