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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Nolan | 2004-08-22 17:35:20 | Re: Greatest/Least functions? |
Previous Message | Tom Lane | 2004-08-22 17:11:58 | Re: Unsupported 3rd-party solutions (Was: Few questions on postgresql (dblink, 2pc, clustering)) |