Thread: Referencing function value inside CASE..WHEN
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
MY_FUNCTION(...) AS my_function,
CASE WHEN my_function = '...' THEN '...' ELSE '...' END
...
...
Thank you in advance.
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
MY_FUNCTION(...) AS my_function,
CASE WHEN my_function = '...' THEN '...' ELSE '...' END
...
...
Thank you in advance.
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: > 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 > > MY_FUNCTION(...) AS my_function, > > CASE WHEN my_function = '...' THEN '...' ELSE '...' END > > ... > > ... > > > Thank you in advance.
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 > SELECT > > MY_FUNCTION(...) AS my_function, > > CASE WHEN my_function = '...' THEN '...' ELSE '...' END > > ... > > ... > > > Thank you in advance.
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/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle