[SQL] LEAST and GREATEST functions?
Stefan Bill
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Josh Berkus
> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Greg Stark
> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Bruno Wolff III
Greg Stark <gss...@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;
Joe Conway
> 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.
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Stefan Bill
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Josh Berkus
> 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Joe Conway
>>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
---------------------------(end of broadcast)---------------------------
Ang Chin Han
--------------enig62B731F22F5EC026CBBFBC49
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------enig62B731F22F5EC026CBBFBC49
Content-Type: application/pgp-signature
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.0 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/AllHNYbTUIgzwfARAoqiAJ94JhhbRTZJwenRzdWU/kp3+7d+kwCgjcdn
tuCoIXEwdsCg33wP37r7pG4=
=DUpb
-----END PGP SIGNATURE-----
--------------enig62B731F22F5EC026CBBFBC49--
Ang Chin Han
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------enigDDD07440B410C556D2675993
Content-Type: application/pgp-signature
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.0 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/AnfqNYbTUIgzwfARAvEZAJ0Rmu2OEitV2wwgMD9O76VB1sgkyACfTGcu
+wqPp25pNy1NxS7p4zw+nHw=
=e1/U
-----END PGP SIGNATURE-----
--------------enigDDD07440B410C556D2675993--
Tom Lane
> 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
Ang Chin Han
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------enigEDF9135096CEAE41D70B673B
Content-Type: application/pgp-signature
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.0 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/AoWKNYbTUIgzwfARAjMAAKDBdA3/yr1GgbqTombxl2xXpIDPwwCgrVEn
WJoHwyusYYeENS0iRlGDly8=
=V9eJ
-----END PGP SIGNATURE-----
--------------enigEDF9135096CEAE41D70B673B--
Joe Conway
> 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 don't think it's worth putting in contrib, but for the archives:
create or replace function make_greatest() returns text as '
declare
v_args int := 32;
v_first text := ''create or replace function greatest(anyelement,
anyelement) returns anyelement as ''''select case when $1 > $2 then $1
else $2 end'''' language ''''sql'''''';
v_part1 text := ''create or replace function greatest(anyelement'';
v_part2 text := '') returns anyelement as ''''select greatest($1,
greatest($2'';
v_part3 text := ''))'''' language ''''sql'''''';
v_sql text;
begin
execute v_first;
for i in 3 .. v_args loop
v_sql := v_part1;
for j in 2 .. i loop
v_sql := v_sql || '',anyelement'';
end loop;
v_sql := v_sql || v_part2;
for j in 3 .. i loop
v_sql := v_sql || '',$'' || j::text;
end loop;
v_sql := v_sql || v_part3;
execute v_sql;
end loop;
return ''OK'';
end;
' language 'plpgsql';
select make_greatest();
Now you should have 31 "greatest" functions, accepting from 2 to 32
arguments. *Not* heavily tested, but seemed to work for me.
regression=# select
greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2);
greatest
----------
1234
(1 row)
I'll leave "least()" as an exercise for the reader ;-)
HTH,
Joe
---------------------------(end of broadcast)---------------------------
Josh Berkus
> 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?
Er, no. Feature freeze was Tuesday. And you haven't submitted a patch yet.
> 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.
Actually, we did ... that was one of 3-4 "killer features" for 7.3
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
Stefan Bill
I solved my problem (for now) by creating a bunch of
overloaded LEAST and GREATEST functions, one for each
datatype. They only take two parameters, but that's
fine for what we're doing.
However, I ran into another, unrelated problem. I
created the LEAST and GREATEST functions as described
above, but when I tried to perform an update statement
comparing two timestamps, I ran into a problem:
UPDATE foo
SET my_timestamp_field =
LEAST(my_timestamp_field,
TO_TIMESTAMP('2003-07-01 12:34:56',
'YYYY-MM-DD HH24:MI:SS'))
WHERE ...
My LEAST function would not work because
my_timestamp_field has a datatype of "timestamp
without time zone", and the TO_TIMESTAMP(...) creates
a "timestamp *with* time zone". I could not find
anything in the documentation about this behavior. I
am running v7.3.2.
All help is appreciated, and please keep up the
discussion about the ability to create functions with
variable number of arguments (LEAST, GREATEST, etc.).
I could see the potential for wanting to write these
in the future.
Cheers,
-Stefan
__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
---------------------------(end of broadcast)---------------------------
Josh Berkus
> My LEAST function would not work because
> my_timestamp_field has a datatype of "timestamp
> without time zone", and the TO_TIMESTAMP(...) creates
> a "timestamp *with* time zone". I could not find
> anything in the documentation about this behavior. I
> am running v7.3.2.
Just do a:
LEAST(my_timestamp_field,
TO_TIMESTAMP('2003-07-01 12:34:56',
'YYYY-MM-DD HH24:MI:SS')::TIMESTAMP WITHOUT TIME ZONE)
with and without time zone are effectively seperate data types with easy
casting.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------