Skip site navigation (1) Skip section navigation (2)

Re: Greatest/Least functions?

From: Joe Conway <mail(at)joeconway(dot)com>
To: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>
Cc: pgsql general list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Greatest/Least functions?
Date: 2004-08-22 17:32:32
Message-ID: 4128D8B0.5050103@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Mike NolanDate: 2004-08-22 17:35:20
Subject: Re: Greatest/Least functions?
Previous:From: Tom LaneDate: 2004-08-22 17:11:58
Subject: Re: Unsupported 3rd-party solutions (Was: Few questions on postgresql (dblink, 2pc, clustering))

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group