Postgres Pro
Downloads
Home   >   mailing lists

Thread: Largest & Smallest Functions

Largest & Smallest Functions

From
Ken Tanzer
Date:
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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Largest & Smallest Functions

From
Ondřej Bouda
Date:
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

That 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

Re: Largest & Smallest Functions

From
Ken Tanzer
Date:
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.

Re: Largest & Smallest Functions

From
Pavel Stehule
Date:


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

Re: Largest & Smallest Functions

From
Ken Tanzer
Date:


On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

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)


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.

Re: Largest & Smallest Functions

From
Pavel Stehule
Date:


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


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!

Ken


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Largest & Smallest Functions

From
Andrew Gierth
Date:
>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> 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 
---------
       3

 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)

Re: Largest & Smallest Functions

From
Pavel Stehule
Date:


čt 8. 11. 2018 v 7:11 odesílatel Andrew Gierth <andrew@tao11.riddles.org.uk> napsal:
>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> 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
---------
       3

It is work with custom functions, not with buildin least, greatest

Regards

Pavel


 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)

Re: Largest & Smallest Functions

From
Ken Tanzer
Date:
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.

Re: Largest & Smallest Functions

From
Andrew Gierth
Date:
>>>>> "Pavel" == Pavel Stehule <pavel.stehule@gmail.com> 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)

Re: Largest & Smallest Functions

From
Pavel Stehule
Date:


čt 8. 11. 2018 v 7:34 odesílatel Andrew Gierth <andrew@tao11.riddles.org.uk> napsal:
>>>>> "Pavel" == Pavel Stehule <pavel.stehule@gmail.com> 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)

Re: Largest & Smallest Functions

From
Pavel Stehule
Date:


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


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!

Ken


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Largest & Smallest Functions

From
Ken Tanzer
Date:
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.