Обсуждение: Largest & Smallest Functions
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;
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;
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Wed, Nov 7, 2018 at 2:46 PM Ondřej Bouda <obouda@email.cz> wrote:
Hi,
> 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?)
LEAST() and GREATEST() expressions do the same thing as yours smallest()
and largest(). See
https://www.postgresql.org/docs/current/functions-conditional.html
Ah, thanks very much! I missed those because I didn't see them as functions, and didn't think about expressions.
But then going back to my Q3, what is the point of the separate date_larger,int2smaller, etc. functions? Are they faster than least/greatest because they are specific to a particular data type?
Now to be a little more serious, if you want a single function to both
support variadic number of arguments AND all of them in a single array,
how could the function decide whether smallest(ARRAY[1,2,3]) shall
return 1 or ARRAY[1,2,3] (which is the smallest out of all arguments)?
Personally I don't have any use cases where I'd be comparing arrays, and so would be happy to have a single array be treated as a list of elements. But I definitely see your point in more general terms!
Thanks again,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
st 7. 11. 2018 v 22:38 odesílatel Ken Tanzer <ken.tanzer@gmail.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 withCREATE FUNCTION largest(VARIADIC anynonarray)but get:ERROR: VARIADIC parameter must be an arraySo 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$
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)
┌──────────┐
│ smallest │
╞══════════╡
│ 1 │
└──────────┘
(1 row)
TIA!Ken--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
You can pass variadic arguments as a arraypostgres=# \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)
That's very helpful and good to know. It's too bad that doesn't work with LEAST/GREATEST, which would eliminate the need for extra functions.
Thanks!
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
čt 8. 11. 2018 v 7:02 odesílatel Ken Tanzer <ken.tanzer@gmail.com> napsal:
On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:You can pass variadic arguments as a arraypostgres=# \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)That's very helpful and good to know. It's too bad that doesn't work with LEAST/GREATEST, which would eliminate the need for extra functions.
These functions are differently implemented - and they are older than support of variadic functions.But implementation of passing a array should be easy.
Pavel
--Thanks!KenAGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
On Wed, Nov 7, 2018 at 10:10 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
But you don't need to create more functions, because you can do this:
select largest(variadic array[1,2,3]);
largest
---------
3
As already pointed out, greatest() and least() exist (though they were
added before VARIADIC was, so they don't use it)
Yeah, I get that my four functions can collapse down to 2. I was just trying to say that if the greatest() and least() expressions did use VARIADIC, that would collapse down to 0 additional functions. :)
Ken> 3) Bonus question--how come all the existing _larger and _smaller
Ken> functions are specific to each data type, as opposed to more
Ken> general smaller/larger functions?
Because it saves looking up the type comparison function and doing an
indirect call.
That makes sense--thanks for the explanation!
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
čt 8. 11. 2018 v 7:02 odesílatel Ken Tanzer <ken.tanzer@gmail.com> napsal:
On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:You can pass variadic arguments as a arraypostgres=# \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)That's very helpful and good to know. It's too bad that doesn't work with LEAST/GREATEST, which would eliminate the need for extra functions.
I sent a patch to pgsql-hackares that allows VARIADIC argument for LEAST/GREATEST
Regards
Pavel
--Thanks!KenAGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
On Thu, Nov 8, 2018 at 7:01 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
postgres=# select smallest(VARIADIC ARRAY[1,2,3]);
┌──────────┐
│ smallest │
╞══════════╡
│ 1 │
└──────────┘
(1 row)That's very helpful and good to know. It's too bad that doesn't work with LEAST/GREATEST, which would eliminate the need for extra functions.I sent a patch to pgsql-hackares that allows VARIADIC argument for LEAST/GREATEST
That's great! Thanks for doing that!
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.