Thread: Evaluate only one CASE WHEN in a select
Hi everybody,
I'm implementing something like this:
SELECT
CASE WHEN add_numbers(t1.main_number,t2.main_number)>100
THEN t1.description1
ELSE t2.description1
END AS number_description1,
CASE WHEN add_numbers(t1.main_number,t2.main_number)>100
THEN t1.description2
ELSE t2.description2
END AS number_description2
FROM table1 t1, table2 t2;
Is there a way to evaluate the 'CASE WHEN' only once?
Thanks
Daniel
dcrespo wrote: > Hi everybody, > > I'm implementing something like this: > > SELECT > CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 > THEN t1.description1 > ELSE t2.description1 > END AS number_description1, > CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 > THEN t1.description2 > ELSE t2.description2 > END AS number_description2 > FROM table1 t1, table2 t2; > > Is there a way to evaluate the 'CASE WHEN' only once? Sure, see the implementation of CASE here: http://www.postgresql.org/docs/8.2/static/functions-conditional.html Not sure what you are looking for, though, since your condition in both CASEs above is exactly the same. -- Guy Rouillier
On Apr 11, 11:35 pm, guyr-...@burntmail.com (Guy Rouillier) wrote: > dcrespo wrote: > > Hi everybody, > > > I'm implementing something like this: > > > SELECT > > CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 > > THEN t1.description1 > > ELSE t2.description1 > > END AS number_description1, > > CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 > > THEN t1.description2 > > ELSE t2.description2 > > END AS number_description2 > > FROM table1 t1, table2 t2; > > > Is there a way to evaluate the 'CASE WHEN' only once? > > Sure, see the implementation of CASE here: > > http://www.postgresql.org/docs/8.2/static/functions-conditional.html > > Not sure what you are looking for, though, since your condition in both > CASEs above is exactly the same. > > -- > Guy Rouillier > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq- Hide quoted text - > > - Show quoted text - They are exactly the same, that's why I want to evaluate it only once and, depending on it, put the corresponding value into two different fields that must be returned, instead of evaluating once for each field. Any insight? Thank you Daniel
"dcrespo" <dcrespo@gmail.com> writes:
> They are exactly the same, that's why I want to evaluate it only once
> and, depending on it, put the corresponding value into two different
> fields that must be returned, instead of evaluating once for each
> field. Any insight?
There's no solution that wouldn't cost you more than double evaluation,
for such a simple expression.
The general solution is to use two levels of SELECT:
select ..., x, x, ...
from (select ..., big-expr as x, ... from ... offset 0) ss;
You need the "offset 0" (which is otherwise a no-op) to prevent the
planner from folding the two selects into a single level and ending up
with two copies of big-expr anyway. The runtime overhead associated
with the extra plan level is about going to eat up whatever you might
save in this example, though with a seriously expensive expression
(for instance, a function that does some fairly expensive SELECT itself)
you might find it worth doing.
regards, tom lane
On Apr 12, 4:30 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: > "dcrespo" <dcre...@gmail.com> writes: > > They are exactly the same, that's why I want to evaluate it only once > > and, depending on it, put the corresponding value into two different > > fields that must be returned, instead of evaluating once for each > > field. Any insight? > > There's no solution that wouldn't cost you more than double evaluation, > for such a simple expression. > > The general solution is to use two levels of SELECT: > > select ..., x, x, ... > from (select ..., big-expr as x, ... from ... offset 0) ss; > > You need the "offset 0" (which is otherwise a no-op) to prevent the > planner from folding the two selects into a single level and ending up > with two copies of big-expr anyway. The runtime overhead associated > with the extra plan level is about going to eat up whatever you might > save in this example, though with a seriously expensive expression > (for instance, a function that does some fairly expensive SELECT itself) > you might find it worth doing. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ Thank you, Tom. In your example, "x,x" seems to be the same value. I don't want that exactly. Here is another example: SELECT CASE WHEN is_odd(t.number) THEN 'Update' ELSE 'Insert' END AS action, CASE WHEN is_odd(t.number) THEN t.number ELSE NULL END AS number, FROM ... ; As you can see, is_odd function (only a function example) is being run twice with exactly the same parameters to put a value into action field and into number field. Since it's the same function call, I want it to be run only once and put the above values into their corresponding fields. My actual function is not that expensive, but has to be run for every retrieved row, obviously. Does this change your explanation? Thank you Daniel
dcrespo wrote: > > SELECT > CASE WHEN is_odd(t.number) THEN 'Update' ELSE 'Insert' END AS > action, > CASE WHEN is_odd(t.number) THEN t.number ELSE NULL END AS number, > FROM ... ; > > As you can see, is_odd function (only a function example) is being run > twice with exactly the same parameters to put a value into action > field and into number field. Since it's the same function call, I want > it to be run only once and put the above values into their > corresponding fields. My actual function is not that expensive, but > has to be run for every retrieved row, obviously. Does this change > your explanation? If your objective is to run a stored procedure once but return several values, look at either defining multiple OUT parameters, or returning a record type. -- Guy Rouillier