Type cast text to int4

Поиск
Список
Период
Сортировка
От M. van Egmond
Тема Type cast text to int4
Дата
Msg-id 46E11B97.9030808@quetalzorg.nl
обсуждение исходный текст
Ответы Re: Type cast text to int4  (Martijn van Oosterhout <kleptog@svana.org>)
Список pgsql-general
Hello everybody,

Im having troubles overriding postgresql's default behaviour of throwing
an error while trying to cast an empty string to int4. I would like to
cast empty strings to a null integer value. I've tried this by creating
my own domain:

CREATE DOMAIN my_int AS integer;

-- Then created a pl/pgsql function that handles my type

CREATE OR REPLACE FUNCTION text2my_int(text)
  RETURNS my_int AS
$BODY$BEGIN
if $1='' then
    return NULL;
end if;
return $1::int4;
END;$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;

-- Then added the typecast for my type.

CREATE CAST (text AS my_int) WITH FUNCTION text2my_int(text) AS IMPLICIT;

-- Testing the new cast:

select ''::my_int;

-- The expected result should be a NULL value, instead i get an ERROR:
--
-- ERROR: invalid input syntax for integer: ""
-- SQL status:22P02


Does anyone have a clue how i can cast empty string to a NULL integer
value by just using a pl/pgsql function?

Thanks!

Matthieu van Egmond



В списке pgsql-general по дате отправления:

Предыдущее
От: "M. van Egmond"
Дата:
Сообщение: Type cast text to int4
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: Type cast text to int4