BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
От | PG Bug reporting form |
---|---|
Тема | BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8 |
Дата | |
Msg-id | 16549-4991fbf36fcec234@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8 |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16549 Logged by: Sławomir Nowakiewicz Email address: slawomir.nowakiewicz@rubix.com PostgreSQL version: 12.1 Operating system: Linux Description: "CASE" try to cast a string to integer when a condition is not met. I checked this function also on PostgreSQL 11.2 - the result is the same. BEGIN; CREATE TABLE public.temp_data_type ( data_type_name text NOT NULL, data_type_storage_type text NOT NULL DEFAULT ''::text ); INSERT INTO public.temp_data_type VALUES ('INTEGER','int'), ('LOOKUP_TABLE','string'); CREATE OR REPLACE FUNCTION public.temp_item_attribute_create(p_datatype text, p_values text[]) RETURNS integer LANGUAGE 'plpgsql' VOLATILE AS $BODY$ DECLARE value_int integer; i integer; BEGIN i:=1; --RAISE NOTICE 'datatype: %', p_datatype; --RAISE NOTICE 'attribute.values[i]: %',p_values[i]; value_int := CASE WHEN p_datatype::TEXT IN (SELECT data_type_name FROM temp_data_type WHERE data_type_storage_type = 'int') THEN p_values[i]::INTEGER ELSE NULL::INTEGER END; RETURN value_int; END; $BODY$; ALTER FUNCTION public.temp_item_attribute_create(text,text[]) OWNER TO postgres; SELECT temp_item_attribute_create('LOOKUP_TABLE','{SHELL}'); --ROLLBACK; ERROR: 22P02: invalid input syntax for type integer: "SHELL" CONTEXT: SQL statement "SELECT CASE WHEN p_datatype::TEXT IN (SELECT data_type_name FROM temp_data_type WHERE data_type_storage_type = 'int') THEN p_values[i]::INTEGER ELSE NULL::INTEGER END" PL/pgSQL function temp_item_attribute_create(text,text[]) line 10 at assignment LOCATION: pg_strtoint32, numutils.c:259
В списке pgsql-bugs по дате отправления: