Subject: Re: [GENERAL] Referencing function value inside CASE..WHEN
From: Martijn van Oosterhout <[email-masked]>
To: lover boi <[email-masked]>
Cc: [email-masked]
Date: Sun, 19 Jun 2011 12:29:45 +0200
List-Id: <pgsql-general.postgresql.org>

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

Yes

> If so, is there a way I can make it execute once?  I tried this but it gave me a "Column my_function does not exist" error.
> 
> SELECT
> 
>   MY_FUNCTION(...) AS my_function,
> 
>   CASE WHEN my_function = '...' 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,
-- 
Martijn van Oosterhout   <[email-masked]>   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. 
>                                       - Charles de Gaulle