Re: Override system-defined cast?

>> Is there any way I can override system-defined casts?
>>
>> Tried
>>
>> create cast (varchar as timestamptz) with function
>> user_timestamptz(varchar) ;
>>
>> and got
>>
>> ERROR: cast from type pg_catalog.varchar to type timestamptz already exists
>>
>> DROP CAST does not work:
>>
>> ERROR: cannot drop cast from character varying to timestamp with time
>> zone because it is required by the database system
>>
>> (or are my permissions insufficient?)
>>
>> Basically my problem is converting '' (empty string) to
>> NULL::timestampz, and built-in cast cannot convert blank string to
>> timestamptz. Maybe I'm wondering up the wrong alley with casts?
>>
>> One solution I can see is create user-defined type (which would be the
>> same timestamptz) and define varchar->mytype cast, but that will require
>> rather extensive changes to database schema. Plus, our user-interface
>> currently relies on PG datatypes to format input/output data.
>>
>> Any suggestions?
>>
>
> How about a function with a CASE statement in it?
>
>

That wouldn't be The Way of The Dragon ;)

Most of my SQL statements are dynamically generated. Using CASE means I
will have to check target field datatype, and apply CASE whenever it's
timestamptz. Rather messy.

I tried defining my own base type using timestamptz _in and _out
functions, and it seems to work. Had to re-create half of my database
objects due to dependencies, but now that it's done it seems to work
quite well.

Peter