Hi,
We have an existing (PHP) code base that is being converted to use
PostgreSQL from MySQL. In most places, our insert and update
statements are formed using single quoted values, and when there is no
value, the empty string is being passed in. PostgreSQL objects to
empty strings for certain column types, particularly numeric columns.
We're trying to get this conversion done quickly, and so a solution
involving customization of string to X type conversion is what we're
after.
What I first tried to do was,
CREATE OR REPLACE FUNCTION varchar_to_int_with_empty_string_handling(varchar)
RETURNS integer AS $$
SELECT CASE
WHEN $1 = '' THEN NULL
ELSE pg_catalog.int4($1)
END
$$ IMMUTABLE LANGUAGE SQL;
DROP CAST IF EXISTS (varchar AS integer);
CREATE CAST (varchar AS integer)
WITH FUNCTION varchar_to_int_with_empty_string_handling(varchar)
AS ASSIGNMENT;
This seems broken, when loading this file a second or subsequent time
(we append mysql compat functions to it as we progress), there is an
error saying pg_catalog.int4 doesn't exist. So somehow, the cast above
is deleting/disabling/hiding pg_catalog.int4?
While experimenting, trying to understand what I'm doing wrong, I ran
this query:
SELECT castsource::regtype, casttarget::regtype,
castfunc::regprocedure, castcontext
FROM pg_cast
WHERE casttarget = 'int'::regtype
and I notice that there isn't any rows specified for converting
varchar or text to int. Which raises the question, if I run:
SELECT '123'::int;
What conversion is actually happening here?
Any answers are much appreciated,
thanks
Dave
