Home > mailing lists

Largest & Smallest Functions - Mailing list pgsql-general

From	Ken Tanzer
Subject	Largest & Smallest Functions
Date	November 8, 2018 00:37:09
Msg-id	CAD3a31WVxY7ARuQ=c0t6dDfjskeHd1Ymv+fNyuz=xYaG4GCXnQ@mail.gmail.com Whole thread Raw
Responses	Re: Largest & Smallest Functions Re: Largest & Smallest Functions Re: Largest & Smallest Functions
List	pgsql-general

Tree view

Hi. Building on the [type]_larger and _smaller functions (and lifting from the documentation), I put together a couple of functions that will take any number of arguments:

CREATE FUNCTION largest(VARIADIC anyarray) RETURNS anyelement AS $$
SELECT max($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION smallest(VARIADIC anyarray) RETURNS anyelement AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL IMMUTABLE;

So far so good. I can do smallest(2,4,7), etc. But to take this a convenient step further, sometimes I want the smallest or largest from values already in an array. So I can create these functions:

CREATE FUNCTION largest_from_array(anyarray) RETURNS anyelement AS $$
SELECT max($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION smallest_from_array(anyarray) RETURNS anyelement AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL IMMUTABLE;

That works, but ideally I'd like both of these function sets to have the same name, and work whether called with an array or a set of values. I tried with

CREATE FUNCTION largest(VARIADIC anynonarray)

but get:

ERROR:  VARIADIC parameter must be an array

So here's my questions:

1) Is there any way to collapse those four functions into two? (Or is there a better way to go about this?)

2) Is there any particular reason functions like that aren't built into Postgres? They seem like they would be useful. (Or maybe I missed them?)

3) Bonus question--how come all the existing _larger and _smaller functions are specific to each data type, as opposed to more general smaller/larger functions?

TIA!

Ken