Thread: LEAST and GREATEST functions?

LEAST and GREATEST functions?

From
Stefan Bill
Date:
Hi,

I know the LEAST and GREATEST functions are not part
of standard SQL, but they sure were handy where I came
from (Oracle-land).

Has anyone written user-defined functions that do the
same thing?

Are there any plans to add these functions as part of
a future version Postgres?

Thanks,

-Stefan


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com


Re: LEAST and GREATEST functions?

From
Josh Berkus
Date:
Stefan,

> I know the LEAST and GREATEST functions are not part
> of standard SQL, but they sure were handy where I came
> from (Oracle-land).
>
> Has anyone written user-defined functions that do the
> same thing?
>
> Are there any plans to add these functions as part of
> a future version Postgres?

Um, what's wrong with MAX and MIN, exactly?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: LEAST and GREATEST functions?

From
Greg Stark
Date:
Josh Berkus <josh@agliodbs.com> writes:

> Stefan,
> 
> > I know the LEAST and GREATEST functions are not part
> > of standard SQL, but they sure were handy where I came
> > from (Oracle-land).
> 
> Um, what's wrong with MAX and MIN, exactly?

MAX and MIN are single-parameter aggregate functions. LEAST and GREATEST are
two-parameter (though in postgres they could be defined for 3 and more
parameters) scalar functions.

eg:

SELECT max(a) FROM bar 

would return a single tuple with the maximum value of a from amongst every
record. whereas:

SELECT greatest(a,b) FROM bar

would return one tuple for every record in the table with a single value
representing the greater of bar.a and bar.b.

You could define your own functions to do this but it would be tiresome to
define one for every datatype.

-- 
greg



Re: LEAST and GREATEST functions?

From
Bruno Wolff III
Date:
On Tue, Jul 01, 2003 at 12:29:16 -0400, Greg Stark <gsstark@mit.edu> wrote:
> 
> SELECT greatest(a,b) FROM bar
> 
> would return one tuple for every record in the table with a single value
> representing the greater of bar.a and bar.b.

You can do this with case.

SELECT CASE WHEN a >= b THEN a ELSE b END FROM bar;


Re: LEAST and GREATEST functions?

From
Joe Conway
Date:
Greg Stark wrote:
> SELECT greatest(a,b) FROM bar
> 
> would return one tuple for every record in the table with a single value
> representing the greater of bar.a and bar.b.
> 
> You could define your own functions to do this but it would be tiresome to
> define one for every datatype.
> 

In 7.4devel (just starting beta) you can do this:

create or replace function greatest(anyelement, anyelement) returns 
anyelement as 'select case when $1 > $2 then $1 else $2 end' language 'sql';

regression=# select greatest(1, 2); greatest
----------        2
(1 row)

regression=# select greatest('b'::text, 'a'); greatest
---------- b
(1 row)

regression=# select greatest(now(), 'yesterday');           greatest
------------------------------- 2003-07-01 13:21:56.506106-07
(1 row)

The cast to text is needed because 'a' and 'b' are really typed as 
unknown, and with polymorphic functions, you need a well defined data type.

So if you had a table:
create table g(f1 text, f2 text);
insert into g values ('a','b');
insert into g values ('c','b');
regression=# select greatest(f1, f2) from g; greatest
---------- b c
(2 rows)

Doesn't help for 7.3.x, but at least you know help is on the way ;-)
Of course, you could always just use the case statement.

Joe



Re: LEAST and GREATEST functions?

From
Stefan Bill
Date:
> Um, what's wrong with MAX and MIN, exactly?

MIN and MAX are aggregate functions, LEAST and
GREATEST are not.  See the examples on the following
table:

foo
A B
- -
1 4
2 3
3 2

> SELECT LEAST(a, b), GREATEST(a, b) FROM foo;

LEAST(a, b) GREATEST(a, b)
----------- --------------
1           4
2           3
2           3

> SELECT MIN(a), MAX(b) FROM foo;

MIN(a) MAX(b)
------ ------
1      4

After further research, I found that the only way to
have a function with a variable number of arguments is
to create N number of overloaded functions, e.g.
CREATE FUNCTION least(int)...
CREATE FUNCTION least(int, int)...
CREATE FUNCTION least(int, int, int)...
...etc...

That sucks, especially since the underlying languages
support variable arguments that will scale to
who-knows-where (see varargs in C, *args in Python,
for starters).  Not only that, but I'd have to create
another N number of functions for different datatypes
(int, float, date, etc.).

In addition to adding the LEAST and GREATEST
functions, the PostgreSQL developers should add the
ability to create user-defined functions with a
variable number of arguments.

Cheers,

-Stefan


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com


Re: LEAST and GREATEST functions?

From
Joe Conway
Date:
Joe Conway wrote:
> In 7.4devel (just starting beta) you can do this:

Actually to correct myself, we just started "feature freeze" for 7.4, 
with beta planned to start on or about July 15th.

Sorry for any confusion caused.

Joe



Re: LEAST and GREATEST functions?

From
Josh Berkus
Date:
Joe,

> create or replace function greatest(anyelement, anyelement) returns
> anyelement as 'select case when $1 > $2 then $1 else $2 end' language
> 'sql';

Way cool.  I'd have to imagine that it would blow up if you did this, though:

select greatest ( 512, now() );

With an "Operator is not defined" error, hey?


-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: LEAST and GREATEST functions?

From
Joe Conway
Date:
Josh Berkus wrote:
>>create or replace function greatest(anyelement, anyelement) returns
>>anyelement as 'select case when $1 > $2 then $1 else $2 end' language
>>'sql';
> 
> Way cool.  I'd have to imagine that it would blow up if you did this, though:
> 
> select greatest ( 512, now() );
> 
> With an "Operator is not defined" error, hey?

It errors out with a type mismatch error:

regression=# select greatest (512, now());
ERROR:  Function greatest(integer, timestamp with time zone) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

Of course none of this is documented yet (because I still owe the 
documentation ;-), but that can be done during feature freeze/beta), but 
the concept of the anyelement data type is that, although it can mean 
literally any data type, any arguments (or return type) so defined have 
to match each other at function call time. So with:  greatest(anyelement, anyelement) returns anyelement
when it gets called, the two arguments *must* be the same data type, and 
the function will return the same type. Any arguments declared with a 
specific datatype (say integer) don't participate in the runtime 
resolution of the polymorphic arguments.

Similarly there is an anyarray data type that is constrained at runtime 
to be an array of anything that was defined as anyelement; e.g.:

create or replace function myelement(anyarray, int) returns anyelement 
as 'select $1[$2]' language 'sql';

regression=# select myelement(array[11,22,33,44,55], 2); myelement
-----------        22
(1 row)

Joe



Re: LEAST and GREATEST functions?

From
Ang Chin Han
Date:
Greg Stark wrote:

> MAX and MIN are single-parameter aggregate functions. LEAST and GREATEST are
> two-parameter (though in postgres they could be defined for 3 and more
> parameters) scalar functions.

If LEAST and GREATEST can accept any number of parameters, wouldn't it 
make sense to code it like the way COALESCE works, rather than defining 
a function for it? This way we don't need define all the various 
functions with different types.

e.g.

SELECT greatest(a, b, c) FROM bar;

becomes

SELECT greatest(a, greatest(b, c)) from bar;

becomes

SELECT  CASE WHEN b < c    THEN      CASE WHEN c < a        THEN a        ELSE c      END    ELSE      CASE WHEN b < a
     THEN a        ELSE b      END  END
 
FROM bar;

From the docs:

COALESCE and NULLIF are just shorthand for CASE expressions. They are 
actually converted into CASE expressions at a very early stage of 
processing, and subsequent processing thinks it is dealing with CASE. 
Thus an incorrect COALESCE or NULLIF usage may draw an error message 
that refers to CASE.

-- 
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 
GNU/Linux 11:30am  up 188 days,  2:35,  5 users,  load average: 5.19, 5.08, 5.02

Re: LEAST and GREATEST functions?

From
Tom Lane
Date:
Ang Chin Han <angch@bytecraft.com.my> writes:
> If LEAST and GREATEST can accept any number of parameters, wouldn't it 
> make sense to code it like the way COALESCE works, rather than defining 
> a function for it? This way we don't need define all the various 
> functions with different types.

But COALESCE is a special feature hard-wired into the parser.  There's
no free lunch --- you pay for your extensibility somewhere.
        regards, tom lane


Re: LEAST and GREATEST functions?

From
Ang Chin Han
Date:
Tom Lane wrote:

> But COALESCE is a special feature hard-wired into the parser.  There's
> no free lunch --- you pay for your extensibility somewhere.

That's what I'm suggesting: hard-wiring LEAST and GREATEST into the 
parser. 7.5, maybe?

The question is: is it worth hard-wiring vs functions? (time passes) 
Doesn't seem to be in SQL92. It's in Oracle, Interbase and MySQL, 
though, says google.

I'd say we need to have LEAST and GREATEST at least somewhere in contrib 
(as functions) if not core, to make transition from other RDBMS to 
postgresql easier.

A brief test shows that we would incur quite a performance penalty (I 
compared COALESCE with coalesce_sql_function) if it isn't hardwiring.

-- 
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 
GNU/Linux  1:30pm  up 188 days,  4:35,  4 users,  load average: 5.03, 5.06, 5.08

Re: LEAST and GREATEST functions?

From
Tom Lane
Date:
Ang Chin Han <angch@bytecraft.com.my> writes:
> I'd say we need to have LEAST and GREATEST at least somewhere in contrib 
> (as functions) if not core, to make transition from other RDBMS to 
> postgresql easier.
> A brief test shows that we would incur quite a performance penalty (I 
> compared COALESCE with coalesce_sql_function) if it isn't hardwiring.

In 7.4 I think that tradeoff will change significantly.  SQL functions
are polymorphic thanks to Joe Conway, and they're inline-able thanks
to me ;-), so there's really no difference between writing the strictly
SQL-compliant

SELECT CASE WHEN a>b THEN a ELSE b END FROM foo;

and writing

create function greatest(anyelement, anyelement) returns anyelement as
'select case when $1>$2 then $1 else $2 end' language sql;

SELECT greatest(a,b) FROM foo;

You do have to create several greatest() functions for different numbers
of arguments, but not one for each datatype you want to handle.

I have not seen enough requests for a native LEAST/GREATEST
implementation to make me think we need to do more than this...
certainly I'd rather spend development effort on general facilities
like polymorphism and inlining than on creating one-use facilities
like built-in LEAST/GREATEST.
        regards, tom lane


Re: LEAST and GREATEST functions?

From
Ang Chin Han
Date:
Tom Lane wrote:

> create function greatest(anyelement, anyelement) returns anyelement as
> 'select case when $1>$2 then $1 else $2 end' language sql;

Any chance of this making it into 7.4's contrib? Maybe with enough 
documentation to make it a tutorial for PostgreSQL's user functions?

> You do have to create several greatest() functions for different numbers
> of arguments, but not one for each datatype you want to handle.

Insignificant, compared with the flexiblity.

> I have not seen enough requests for a native LEAST/GREATEST
> implementation to make me think we need to do more than this...
> certainly I'd rather spend development effort on general facilities
> like polymorphism and inlining than on creating one-use facilities
> like built-in LEAST/GREATEST.

Nice. It would speed up our current functions too. Thanks, developers, 
esp. Tom and Joe for this!

Wow, less reasons to use VIEWs when CREATE FUNCTION can be just as fast, 
but more flexible. Can't wait, IMHO, the advocacy people can and should 
be promoting this(functions returning sets, and how it can be used) as a 
killer feature for 7.3 and 7.4. I know I was pretty happy to discover 
that gem  lurking in the documentation in 7.3.

-- 
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 
GNU/Linux  2:30pm  up 188 days,  5:35,  4 users,  load average: 5.04, 5.15, 5.16