From: | jaksits(dot)tibor(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #12273: CASE Expression BUG |
Date: | 2014-12-18 14:44:36 |
Message-ID: | 20141218144436.1237.96696@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 12273
Logged by: Tibor Jaksits
Email address: jaksits(dot)tibor(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-12-18 15:51:34 | Re: Create into temp table as select doesn set "found" |
Previous Message | Jan Hink | 2014-12-18 09:30:51 | Create into temp table as select doesn set "found" |