Re: BUG #12273: CASE Expression BUG

jaksits(dot)tibor(at)gmail(dot)com writes:
> CREATE OR REPLACE FUNCTION __is_numeric_test_does_not_work(a_text_param
> text)
> RETURNS double precision AS
> $BODY$DECLARE
> ret double precision;
> BEGIN
> SELECT __is_numeric_test
> INTO ret
> FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param) THEN
> a_text_param::double precision ELSE 0.0::double precision END));
> RETURN ret;
> END;$BODY$
> LANGUAGE plpgsql VOLATILE;

> But the query "SELECT * FROM __is_numeric_test_does_not_work('')" I get an
> error message:
> invalid input syntax for type double precision: ""

You didn't show us what is_numeric() is, so it's impossible to reproduce
this example, but I imagine what is happening is that the value of
a_text_param is being substituted into the SELECT as a text constant, and
then constant-folding leads to attempting to simplify a_text_param::double
precision immediately.

We're unlikely to change this, because it would cripple optimization
attempts. The fact that const-simplification doesn't happen in the other
way you wrote the function is not more-correct behavior, it's just an
implementation artifact that you shouldn't rely on. What you need to do
is code this as an if-then-else sequence, not CASE, so that you don't
attempt to evaluate any expressions with undefined constant
subexpressions.

regards, tom lane