From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Largest & Smallest Functions |
Date: | 2018-11-07 21:37:09 |
Message-ID: | CAD3a31WVxY7ARuQ=c0t6dDfjskeHd1Ymv+fNyuz=xYaG4GCXnQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Ondřej Bouda | 2018-11-07 22:46:46 | Re: Largest & Smallest Functions |
Previous Message | Tom Lane | 2018-11-07 15:27:53 | Re: recursion in plpgsql |