Referencing function value inside CASE..WHEN

You can try using a CTE/With clause. Failing that use a function with SELECT INTO...

On Jun 18, 2011, at 22:45, lover boi <lover-boi@live.co.uk> wrote:

Hello

2011/6/19 lover boi <lover-boi@live.co.uk>:

Hi,

In the following statement, does the custom function get executed twice?

SELECT
  MY_FUNCTION(...),
  CASE WHEN MY_FUNCTION(...) = '...' THEN '...' ELSE '...' END
...
...

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 x, CASE WHEN x THEN .. ELSE .. END FROM (SELECT fce(..) x FROM foo) s;

Regards

Pavel Stehule

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 <kleptog@svana.org> http://svana.org/kleptog/

This message has been digitally signed by the sender.