473,569 Members | 2,812 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
8 7726
> 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
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...7/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(anyele ment,
anyelement) returns anyelement as ''''select case when $1 > $2 then $1
else $2 end'''' language ''''sql'''''';
v_part1 text := ''create or replace function greatest(anyele ment'';
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,1 0,1234,2,3,4,5, 66,7,8,9,10,1,2 7,3,4,5,6,347,8 ,9,10,1,2);
greatest
----------
1234
(1 row)
-----------------------------

Here are more examples:

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

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

regression=# explain analyze select
greatest(112,2, 3,4,5,6,7,8,9,1 0,1234,2,3,4,5, 66,7,8,9,10,1,2 7,3,4,5,6,347,8 ,9,10,1,2);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.0 1 rows=1 width=0) (actual time=0.007..0.0 09
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3
> > 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #4
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 <kl*****@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.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBKfEkY5T wig3Ge+YRAgz/AJ0ZviQq83ftrmQ dlsjIQV3fFXIqnA Cfb6dZ
YCg81eIEUCvZE7X NdXpdBO0=
=DhWT
-----END PGP SIGNATURE-----

Nov 23 '05 #5
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #7
> > 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_a rray_of_int
from plpgsql_method_ returning_custo m_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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #8
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_a rray_of_int
from plpgsql_method_ returning_custo m_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)).

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

99
5851
by: David MacQuigg | last post by:
I'm not getting any feedback on the most important benefit in my proposed "Ideas for Python 3" thread - the unification of methods and functions. Perhaps it was buried among too many other less important changes, so in this thread I would like to focus on that issue alone. I have edited the Proposed Syntax example below to take out the...
72
5331
by: Mel | last post by:
Are we going backwards ? (please excuse my spelling...) In my opinion an absolute YES ! Take a look at what we are doing ! we create TAGS, things like <H1> etc. and although there are tools (dreamweaver and the like), they are all at the lowest level of programming (something like assembly as oposed to C++ etc.). These tools create...
3
2618
by: Michael Schneider | last post by:
Hello All, I am comming back to python after being away for several years. I would like to use weak refs in an observer pattern implementation. The problme that I have seems to be that weakrefs can't manage functions. ------------------- from docs: http://www.python.org/doc/current/lib/module-weakref.html
7
4170
by: Mathew Butler | last post by:
Suppose I have a table t with columns id, col1, col2, col3, col4, col5, col6 all numeric. I want to query the table and for each value of col<x> in the resultset I want to identify the largest value in each of the columns. For example, with the following dataset: id, col1, col2, col3, col4, col5, col6 1,2,3,4,5,6,7 2,8,6,5,4,3,2...
4
13680
by: sdlt85 | last post by:
Hi, Can someone help me with an idea on how to start writing a C++ code for generating greatest common divisor and the linear combination of two intergers represented as gcd(m, n)= mx + ny and adding them it will give us the greatest common divisor and I need to use the extended Euclidean algorithm. I already have the code for the greatest...
3
5688
by: stressedstudent | last post by:
I dont know where I am going wrong so I dont know which part to post, this is what I have, can anyone help me figure out where I am going wrong? THanks for any and all help. // into to c++ // This program should use a loop that lets the user enter a series of integers. // The user should enter -99 to signal the end of the series. // After...
2
2327
by: Shawn Minisall | last post by:
I just wrote a program to let the user input a series of whole numbers and tell them which is least and which is greatest based off of a menu. However, the menu isn't kicking in after they pick a number. I included a while statement for a loop just for the menu and compared it to my other programs that have a similar setup and are working,...
0
7619
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7931
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7984
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6290
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5515
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5228
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3663
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2119
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1230
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.