Обсуждение: Type cast text to int4

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

On Fri, Sep 07, 2007 at 11:36:23AM +0200, M. van Egmond wrote:
> 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:

Your problem is that ''::myint does not call your cast function, it
calls the input function for your basetype, which a domain can't
override. Your options are to actually make a new basetype, which isn't
too hard. Or, you can use ''::text::myint which probably will call your
cast function.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Could you please give me the correct syntax to create a basetype using
pl/pgsql functions instead of external functions?
Or is this impossible?

Thanks.

Martijn van Oosterhout schreef:
> On Fri, Sep 07, 2007 at 11:36:23AM +0200, M. van Egmond wrote:
>
>> 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:
>>
>
> Your problem is that ''::myint does not call your cast function, it
> calls the input function for your basetype, which a domain can't
> override. Your options are to actually make a new basetype, which isn't
> too hard. Or, you can use ''::text::myint which probably will call your
> cast function.
>
> Have a nice day,
>

On Fri, Sep 07, 2007 at 01:40:57PM +0200, M. van Egmond wrote:
> Could you please give me the correct syntax to create a basetype using
> pl/pgsql functions instead of external functions?
> Or is this impossible?

It's not possible. You need to be able to deal with raw C strings and
internal datatypes, which pl/pgsql can't do.

What you're trying to do has been done before, so you should check the
archives. In general though you should really just fix the source to
produce valid SQL standard output.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.