Search Postgresql Archives

Re: Largest & Smallest Functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





st 7. 11. 2018 v 22:38 odesílatel Ken Tanzer <ken.tanzer@xxxxxxxxx> 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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux