Re: Largest & Smallest Functions

st 7. 11. 2018 v 22:38 odesílatel Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> napsal:

> 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?)
>

The variadic parameters should not be a arrays - can be of "any" type. But
this functionality is available only for C language functions.

> 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?
>

You can pass variadic arguments as a array

postgres=# \sf smallest
CREATE OR REPLACE FUNCTION public.smallest(VARIADIC anyarray)
RETURNS anyelement
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$function$

postgres=# select smallest(VARIADIC ARRAY[1,2,3]);
┌──────────┐
│ smallest │
╞══════════╡
│ 1 │
└──────────┘
(1 row)

>
> 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.
>