Largest & Smallest Functions

13 messages
Open this post in threaded view
|

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;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 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 systemBy and for non-profitshttp://agency-software.org/[hidden email](253) 245-3801learn more about AGENCY orfollow the discussion.
Open this post in threaded view
|

Re: Largest & Smallest Functions

 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.htmlThat might also answer the first question - just drop smallest() and largest() and you will get two functions instead of four :-) 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)? I would suggest not to declare such overloaded function even if it was possible, as it might confuse the reader easily. Instead, I would go for SELECT min(u) FROM unnest(ARRAY[1,2,3]) u or just define a separate least_array() / greatest_array() variant. Regards, Ondřej Bouda
Open this post in threaded view
|

Re: Largest & Smallest Functions

 On Wed, Nov 7, 2018 at 2:46 PM Ondřej Bouda <[hidden email]> 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 systemBy and for non-profitshttp://agency-software.org/[hidden email](253) 245-3801learn more about AGENCY orfollow the discussion.
Open this post in threaded view
|

Re: Largest & Smallest Functions

 In reply to this post by Ken Tanzer st 7. 11. 2018 v 22:38 odesílatel Ken Tanzer <[hidden email]> 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 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 IMMUTABLEAS \$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 systemBy and for non-profitshttp://agency-software.org/[hidden email](253) 245-3801learn more about AGENCY orfollow the discussion.
Open this post in threaded view
|

Re: Largest & Smallest Functions

 On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule <[hidden email]> wrote:You can pass variadic arguments as a array postgres=# \sf smallest CREATE OR REPLACE FUNCTION public.smallest(VARIADIC anyarray) RETURNS anyelement LANGUAGE sql IMMUTABLEAS \$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 systemBy and for non-profitshttp://agency-software.org/[hidden email](253) 245-3801learn more about AGENCY orfollow the discussion.
Open this post in threaded view
|

Re: Largest & Smallest Functions

 čt 8. 11. 2018 v 7:02 odesílatel Ken Tanzer <[hidden email]> napsal:On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule <[hidden email]> wrote:You can pass variadic arguments as a array postgres=# \sf smallest CREATE OR REPLACE FUNCTION public.smallest(VARIADIC anyarray) RETURNS anyelement LANGUAGE sql IMMUTABLEAS \$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.PavelThanks!Ken-- AGENCY Software  A Free Software data systemBy and for non-profitshttp://agency-software.org/[hidden email](253) 245-3801learn more about AGENCY orfollow the discussion.
Open this post in threaded view
|

Re: Largest & Smallest Functions

Open this post in threaded view
|

Re: Largest & Smallest Functions

 čt 8. 11. 2018 v 7:11 odesílatel Andrew Gierth <[hidden email]> napsal:>>>>> "Ken" == Ken Tanzer <[hidden email]> writes:  Ken> Hi. Building on the [type]_larger and _smaller functions (and  Ken> lifting from the documentation), I put together a couple of  Ken> functions that will take any number of arguments:  Ken> CREATE FUNCTION largest(VARIADIC anyarray) RETURNS anyelement AS  Ken> \$\$ SELECT max(\$1[i]) FROM generate_subscripts(\$1, 1) g(i); \$\$  Ken> LANGUAGE SQL IMMUTABLE;  Ken> So far so good. I can do smallest(2,4,7), etc. But to take this a  Ken> convenient step further, sometimes I want the smallest or largest  Ken> from values already in an array. So I can create these functions: But you don't need to create more functions, because you can do this: select largest(variadic array[1,2,3]);  largest ---------        3It is work with custom functions, not with buildin least, greatestRegardsPavel  Ken> So here's my questions:  Ken> 1) Is there any way to collapse those four functions into two? (Or  Ken> is there a better way to go about this?) See above  Ken> 2) Is there any particular reason functions like that aren't built  Ken> into Postgres? They seem like they would be useful. (Or maybe I  Ken> missed them?) As already pointed out, greatest() and least() exist (though they were added before VARIADIC was, so they don't use it)  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. -- Andrew (irc:RhodiumToad)
Open this post in threaded view
|

Re: Largest & Smallest Functions

Open this post in threaded view
|

Re: Largest & Smallest Functions

 In reply to this post by Pavel Stehule >>>>> "Pavel" == Pavel Stehule <[hidden email]> writes:  Pavel> The variadic parameters should not be a arrays - can be of "any"  Pavel> type. But this functionality is available only for C language  Pavel> functions. You mean (VARIADIC "any")?  - that is not actually restricted to C language functions, any pl/* handler can choose to support it (it's just that all the built-in ones don't). -- Andrew (irc:RhodiumToad)
Open this post in threaded view
|

Re: Largest & Smallest Functions

 čt 8. 11. 2018 v 7:34 odesílatel Andrew Gierth <[hidden email]> napsal:>>>>> "Pavel" == Pavel Stehule <[hidden email]> writes:  Pavel> The variadic parameters should not be a arrays - can be of "any"  Pavel> type. But this functionality is available only for C language  Pavel> functions. You mean (VARIADIC "any")?  - that is not actually restricted to C language functions, any pl/* handler can choose to support it (it's just that all the built-in ones don't).maybe - I am sure, so plpgsql and sql cannot be used. -- Andrew (irc:RhodiumToad)