Thread: 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
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
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;
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
> 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
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@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
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@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
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