BUG #12273: CASE Expression BUG

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

Responses

Browse pgsql-bugs by date

  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"