The following bug has been logged on the website:
Bug reference: 12273
Logged by: Tibor Jaksits
Email address: jaksits.tibor@gmail.com
PostgreSQL version: 9.3.4
Operating system: Linux Debian 64-bit 4.7.2-5
Description:
I created a simple function that returns the number received parameter.
CREATE OR REPLACE FUNCTION __is_numeric_test(a_double_param double
precision)
RETURNS double precision AS
$BODY$DECLARE
BEGIN
RETURN a_double_param;
END;$BODY$
LANGUAGE plpgsql VOLATILE;
I created two other functions:
CREATE OR REPLACE FUNCTION __is_numeric_test_work(a_text_param text)
RETURNS double precision AS
$BODY$DECLARE
BEGIN
RETURN __is_numeric_test((CASE WHEN is_numeric(a_text_param) THEN
a_text_param::double precision ELSE 0.0::double precision END));
END;$BODY$
LANGUAGE plpgsql VOLATILE;
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;
The return value of the "SELECT * FROM __is_numeric_test_work('')" query is
0 (correct operation)
But the query "SELECT * FROM __is_numeric_test_does_not_work('')" I get an
error message:
invalid input syntax for type double precision: ""
CONTEXT: SQL statement "SELECT __is_numeric_test
FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param)
THEN a_text_param::double precision ELSE 0.0::double precision END))"
PL/pgSQL function __is_numeric_test_does_not_work(text) line 5 at SQL
statement
********** Error **********
ERROR: invalid input syntax for type double precision: ""
SQL state: 22P02
Context: SQL statement "SELECT __is_numeric_test
FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param)
THEN a_text_param::double precision ELSE 0.0::double precision END))"
PL/pgSQL function __is_numeric_test_does_not_work(text) line 5 at SQL
statement