From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, Stefan Bill <sjb26(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: LEAST and GREATEST functions? |
Date: | 2003-07-01 20:22:55 |
Message-ID: | 3F01ED9F.3060704@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-07-01 22:32:18 | Re: mergejoin error message executing in 7.2 |
Previous Message | Bruno Wolff III | 2003-07-01 20:22:07 | Re: LEAST and GREATEST functions? |