X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 8C669310DEC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Jul 2003 03:54:57 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 57893-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Jul 2003 00:54:47 -0300 (ADT)
Received: from cerebro.int-bytecraft.com (unknown [219.93.229.26])
	by svr1.postgresql.org (Postfix) with ESMTP id E2F0B310E7D
	for <pgsql-sql@postgresql.org>; Wed,  2 Jul 2003 00:54:44 -0300 (ADT)
Received: from bytecraft.com.my (home.int-bytecraft.com [127.0.0.1])
	by cerebro.int-bytecraft.com (8.11.6/8.11.6) with ESMTP id h623pfq13803;
	Wed, 2 Jul 2003 11:51:47 +0800
Message-ID: <3F025942.6000507@bytecraft.com.my>
Date: Wed, 02 Jul 2003 12:02:10 +0800
From: Ang Chin Han <angch@bytecraft.com.my>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Greg Stark <gsstark@mit.edu>
Cc: Josh Berkus <josh@agliodbs.com>, Stefan Bill <sjb26@yahoo.com>,
	pgsql-sql@postgresql.org
Subject: Re: LEAST and GREATEST functions?
References: <20030630201057.14507.qmail@web13905.mail.yahoo.com>
	<200306302032.23113.josh@agliodbs.com>
	<87d6gu2otf.fsf@stark.dyndns.tv>
In-Reply-To: <87d6gu2otf.fsf@stark.dyndns.tv>
X-Enigmail-Version: 0.76.1.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig62B731F22F5EC026CBBFBC49"
X-Archive-Number: 200307/33
X-Sequence-Number: 14147

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig62B731F22F5EC026CBBFBC49
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Greg Stark wrote:

> MAX and MIN are single-parameter aggregate functions. LEAST and GREATEST are
> two-parameter (though in postgres they could be defined for 3 and more
> parameters) scalar functions.

If LEAST and GREATEST can accept any number of parameters, wouldn't it 
make sense to code it like the way COALESCE works, rather than defining 
a function for it? This way we don't need define all the various 
functions with different types.

e.g.

SELECT greatest(a, b, c) FROM bar;

becomes

SELECT greatest(a, greatest(b, c)) from bar;

becomes

SELECT
   CASE WHEN b < c
     THEN
       CASE WHEN c < a
         THEN a
         ELSE c
       END
     ELSE
       CASE WHEN b < a
         THEN a
         ELSE b
       END
   END
FROM bar;


 From the docs:

COALESCE and NULLIF are just shorthand for CASE expressions. They are 
actually converted into CASE expressions at a very early stage of 
processing, and subsequent processing thinks it is dealing with CASE. 
Thus an incorrect COALESCE or NULLIF usage may draw an error message 
that refers to CASE.

-- 
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 
GNU/Linux
  11:30am  up 188 days,  2:35,  5 users,  load average: 5.19, 5.08, 5.02

--------------enig62B731F22F5EC026CBBFBC49
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.0 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/AllHNYbTUIgzwfARAoqiAJ94JhhbRTZJwenRzdWU/kp3+7d+kwCgjcdn
tuCoIXEwdsCg33wP37r7pG4=
=DUpb
-----END PGP SIGNATURE-----

--------------enig62B731F22F5EC026CBBFBC49--