Thread: Greatest/Least functions?

As far as I can tell, Postgres has no equivalent to greatest and least
functions in Oracle.  Yes, you can do the same thing with a case statement,
but at the expense of writing MUCH longer SQL statements.

Is this something that is on or can be added to the 'to do' list?

I could write a series of user-defined functions to do specific
comparisons (such as comparing several dates and returning the greatest
one) but there doesn't appear to be a way to write a user function with a
variable number of parameters, either, so I guess I'd have to define a
series of them with 2,3,4,... parameters.
--
Mike Nolan

> As far as I can tell, Postgres has no equivalent to greatest and least
> functions in Oracle.
Doesn't max/min() do that ? Note that I know nothing about
greatest/least in Oracle.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Mike Nolan wrote:
> As far as I can tell, Postgres has no equivalent to greatest and least
> functions in Oracle.  Yes, you can do the same thing with a case statement,
> but at the expense of writing MUCH longer SQL statements.
>
> Is this something that is on or can be added to the 'to do' list?
>
> I could write a series of user-defined functions to do specific
> comparisons (such as comparing several dates and returning the greatest
> one) but there doesn't appear to be a way to write a user function with a
> variable number of parameters, either, so I guess I'd have to define a
> series of them with 2,3,4,... parameters.

There was a thread on this last year in July -- see:
http://archives.postgresql.org/pgsql-sql/2003-07/msg00001.php

It doesn't seem to have made it into the archives, but I posted this
solution to the SQL list on 2 July, 2003:
-----------------------------
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)
-----------------------------

Here are more examples:

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

regression=# select greatest(now(),'today','tomorrow');
         greatest
------------------------
  2004-08-23 00:00:00-07
(1 row)

regression=# explain analyze 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);
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.007..0.009
rows=1 loops=1)
  Total runtime: 0.049 ms
(2 rows)


SQL function inlining in pg >=7.4 rewrites ensures there isn't even
function call overhead to be concerned with.

HTH,

Joe

> > As far as I can tell, Postgres has no equivalent to greatest and least
> > functions in Oracle.
> Doesn't max/min() do that ? Note that I know nothing about
> greatest/least in Oracle.

No, max/min are aggregate functions.  Greatest allows you to select
the largest of a series of terms.

Here's a simple example:

greatest(1,2,3,4,5,6) would return 6

Here's a bit more useful one:

greatest(field1,field2,field3) would return the largest value from the
three supplied fields from the current row.

Writing a case statement to select the largest from among 3 or more
values gets a bit complicated.
--
Mike Nolan

On Sun, Aug 22, 2004 at 12:35:20PM -0500, Mike Nolan wrote:
> > > As far as I can tell, Postgres has no equivalent to greatest and least
> > > functions in Oracle.
> > Doesn't max/min() do that ? Note that I know nothing about
> > greatest/least in Oracle.
>
> No, max/min are aggregate functions.  Greatest allows you to select
> the largest of a series of terms.
>
> Here's a simple example:
>
> greatest(1,2,3,4,5,6) would return 6
>
> Here's a bit more useful one:
>
> greatest(field1,field2,field3) would return the largest value from the
> three supplied fields from the current row.

Postgresql does however have the 2-argument versions:

int4larger, int4smaller
floatlarger, floatsmaller

Not seen them mentioned much, but they're very useful... Of course,
after a while even:

int4larger( int4larger( field1, field2 ), int4larger( field3, field4 ) )

gets tiring.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Hi,

The following works :

db=# select 1 = ANY ('{1,2,3}'::int[]);
 ?column?
----------
 t
(1 row)


This doesn't :

db=# select 1 = ANY (select '{1,2,3}'::int[]);
ERROR:  operator does not exist: integer = integer[]
HINT:  No operator matches the given name and argument type(s). You may need
to add explicit type casts.

Using an extra case, the above can easily be made to work :

db=# select 1 = ANY ((select '{1,2,3}'::int[])::int[]);
 ?column?
----------
 t
(1 row)


I'm just wondering why the array returned by the inner select is not casted by
ANY() automagically?


db=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)





--
Best,




Frank.

On Mon, 23 Aug 2004, Frank van Vugt wrote:

> The following works :
>
> db=# select 1 = ANY ('{1,2,3}'::int[]);
>  ?column?
> ----------
>  t
> (1 row)
>
>
> This doesn't :
>
> db=# select 1 = ANY (select '{1,2,3}'::int[]);
> ERROR:  operator does not exist: integer = integer[]
> HINT:  No operator matches the given name and argument type(s). You may need
> to add explicit type casts.
>
> Using an extra case, the above can easily be made to work :
>
> db=# select 1 = ANY ((select '{1,2,3}'::int[])::int[]);
>  ?column?
> ----------
>  t
> (1 row)
>
>
> I'm just wondering why the array returned by the inner select is not casted by
> ANY() automagically?


Barring the cast syntax and such, the first and last query would I believe
be illegal in SQL92/99, so we defined useful behavior for them for this
case.  The second query looks to me to be of the form = ANY (table
subquery) which already had defined behavior by spec. Changing it to act
like the first or last would break that spec behavior.

> > works =# select 1 = ANY ('{1,2,3}'::int[]);
> > doesn't work =# select 1 = ANY (select '{1,2,3}'::int[]);
> > works =# select 1 = ANY ((select '{1,2,3}'::int[])::int[]);

I may be misinterpreting your reply but.....

My real-world application has a set-returning PL/pgSQL function for which I
created a type, so the function is returning rows of this type. One of the
fields in this type is an array of int.

> The second query looks to me to be of the form = ANY (table
> subquery) which already had defined behavior by spec.

Yes, what I want is to be able to do something like:

select some_fields
from some_table
where some_int = ANY(
    select field_of_type_array_of_int
    from plpgsql_method_returning_custom_type
    where we_just_return_a_single_record);

But this won't work, so I'm not quite getting what you mean by 'which already
had defined behavior by spec'

> Changing it to act like the first or last would break that spec behavior.

Ok, but I'm mainly looking for the 'proper' way to make this work, not
necessarily using a syntax like the first or last example.




--
Best,




Frank.

On Mon, 23 Aug 2004, Frank van Vugt wrote:

> > > works =# select 1 = ANY ('{1,2,3}'::int[]);
> > > doesn't work =# select 1 = ANY (select '{1,2,3}'::int[]);
> > > works =# select 1 = ANY ((select '{1,2,3}'::int[])::int[]);
>
> I may be misinterpreting your reply but.....
>
> My real-world application has a set-returning PL/pgSQL function for which I
> created a type, so the function is returning rows of this type. One of the
> fields in this type is an array of int.
>
> > The second query looks to me to be of the form = ANY (table
> > subquery) which already had defined behavior by spec.
>
> Yes, what I want is to be able to do something like:
>
> select some_fields
> from some_table
> where some_int = ANY(
>     select field_of_type_array_of_int
>     from plpgsql_method_returning_custom_type
>     where we_just_return_a_single_record);
>
> But this won't work, so I'm not quite getting what you mean by 'which already
> had defined behavior by spec'

SQL92/99 basically defines
 A = ANY (table subquery) to mean
  For each row returned by the subquery, compare A to the column using the
   = operator

We defined on top of that something like
 A = ANY (array expression) to mean
  For each element in the array compare A to the array element using the =
   operator.

If we made, A = ANY (select arraycol ...) to mean the latter, queries that
 were using it as the former would change meaning from their already
 defined SQL behavior. Perhaps if you wanted to define it as <non array
 type> = ANY (select arraycol ...) it might be okay, but right now
 changing that would mean that you couldn't do
  select arraycol = ANY(select arraycol from table)

I think your third query (with the cast) would be the "correct" way to
indicate the intent. That is effectively
A = ANY (CAST(scalar subquery AS array type)).