Re: Override system-defined cast?

On Thu, Oct 12, 2006 at 12:18:48PM +0300, Peter wrote:
> 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?

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!