Re: LEAST and GREATEST functions?

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

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

> 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

Josh Berkus <josh(at)agliodbs(dot)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

On Tue, Jul 01, 2003 at 12:29:16 -0400,
Greg Stark <gsstark(at)mit(dot)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;

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

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

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

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

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

Ang Chin Han <angch(at)bytecraft(dot)com(dot)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

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

Ang Chin Han <angch(at)bytecraft(dot)com(dot)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

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