Thread: 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?

TIA,
Peter

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@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

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