X-POP3-Server: Host ([mail.hitechroboticsystemz.com]) 	by hitechgears.com (PostMaster POP3 Login [hrs@hitechroboticsystemz.com] [192.168.0.25]); 	Sun, 19 Jun 2011 16:32:11 +0530
Return-Path: <pgsql-general-owner+m176984@postgresql.org>
Received: from mx1.hub.org [200.46.208.106] by dr178.cyberspaceindia.com with SMTP;
   Sun, 19 Jun 2011 16:20:26 +0530
Received: from postgresql.org (mail.postgresql.org [200.46.204.86])
	by mx1.hub.org (Postfix) with ESMTP id 8205027FBB02;
	Sun, 19 Jun 2011 07:50:13 -0300 (ADT)
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 4A812B5DBDA
	for <pgsql-general-postgresql.org@mail.postgresql.org>; Sun, 19 Jun 2011 07:49:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
 by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024)
 with ESMTP id 43585-03
 for <pgsql-general-postgresql.org@mail.postgresql.org>;
 Sun, 19 Jun 2011 10:49:29 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from svana.org (svana.org [113.212.99.187])
	by mail.postgresql.org (Postfix) with ESMTP id C51C6B5DBC0
	for <pgsql-general@postgresql.org>; Sun, 19 Jun 2011 07:49:28 -0300 (ADT)
Received: from kleptog by svana.org with local (Exim 4.72)
	(envelope-from <kleptog@svana.org>)
	id 1QYFGD-0000zc-B1; Sun, 19 Jun 2011 20:29:45 +1000
Date: Sun, 19 Jun 2011 12:29:45 +0200
From: Martijn van Oosterhout <kleptog@svana.org>
To: lover boi <lover-boi@live.co.uk>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Referencing function value inside CASE..WHEN
Message-ID: <20110619102944.GB3197@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
Mail-Followup-To: lover boi <lover-boi@live.co.uk>,
	pgsql-general@postgresql.org
References: <BLU155-W1661F2D893B14654AED26CD56F0@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="UHN/qo2QbUvPLonB"
Content-Disposition: inline
In-Reply-To: <BLU155-W1661F2D893B14654AED26CD56F0@phx.gbl>
X-PGP-Key-ID: Length=1024; ID=0x6F0B424C
X-PGP-Key-Fingerprint: 34A8 8EAC F902 8C8D CA72  DCC7 201E DB34 6F0B 424C
User-Agent: Mutt/1.5.20 (2009-06-14)
X-SA-Exim-Connect-IP: <locally generated>
X-SA-Exim-Mail-From: kleptog@svana.org
X-SA-Exim-Scanned: No (on svana.org); SAEximRunCond expanded to false
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
 T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Mailing-List: pgsql-general
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org
X-Declude-Sender: pgsql-general-owner+m176984@postgresql.org [200.46.208.106]
X-Declude-Spoolname: 43062720.eml
X-Declude-RefID: str=0001.0A090206.4DFDD47D.0039:SCGSTAT545980,ss=1,fgs=1024
X-Declude-Note: Scanned by Declude 4.10.72 "http://www.declude.com/x-note.htm"
X-Declude-Scan: Incoming Score [-4] at 16:20:40 on 19 Jun 2011
X-Declude-Tests: NOABUSE [2], SUBCHARS-55 [1], FROMNOMATCH [2], HAM-INDICATOR [-8], FILTER-SUBJECT [2]
X-Country-Chain: AUSTRALIA->PANAMA->destination
X-Declude-Code: 1f
X-HELO: mx1.hub.org
X-Identity: 200.46.208.106 | mx1.hub.org | postgresql.org
X-Rcpt-To: <anisha.kaul@hitechroboticsystemz.com>
X-SmarterMail-Spam: Declude: -4
X-SmarterMail-TotalSpamWeight: -4

--UHN/qo2QbUvPLonB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Sun, Jun 19, 2011 at 10:45:12AM +0800, lover boi wrote:
> In the following statement, does the custom function get executed twice?
>=20
> SELECT
>   MY_FUNCTION(...),
>   CASE WHEN MY_FUNCTION(...) =3D '...' THEN '...' ELSE '...' END

Yes

> If so, is there a way I can make it execute once?  I tried this but it ga=
ve me a "Column my_function does not exist" error.
>=20
> SELECT
>=20
>   MY_FUNCTION(...) AS my_function,
>=20
>   CASE WHEN my_function =3D '...' THEN '...' ELSE '...' END

You almost had it right, there is another syntax for CASE:

CASE MY_FUNCTION(...) WHEN 'foo' THEN ... WHEN 'bar' THEN ... ELSE ... END;

http://www.postgresql.org/docs/8.4/static/functions-conditional.html

Have a nice day,
--=20
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.=20
>                                       - Charles de Gaulle

--UHN/qo2QbUvPLonB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iD8DBQFN/c+YIB7bNG8LQkwRAjlIAJ0WBUeRzINFWwj3E1yVoMuXEjcIFQCeJULj
SL6Z+PPkZ9RQAbB4nD4GUwo=
=SY3E
-----END PGP SIGNATURE-----

--UHN/qo2QbUvPLonB--
