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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
