Re: implicit cast of empty string to timestamp

How much trouble am I going to get into by modifying the pg_cast table
to call my function instead?

I created this function:

CREATE OR REPLACE FUNCTION pg_catalog.mssql_timestamp (text)
RETURNS timestamp
LANGUAGE SQL
AS $$
SELECT CASE WHEN $1 = '' THEN NULL ELSE pg_catalog.timestamp($1) END
$$;

And then updated the pg_cast table with this statement:

UPDATE pg_cast
SET castfunc = (SELECT Oid FROM pg_proc WHERE proname =
'mssql_timestamp') WHERE castfunc = (SELECT Oid FROM pg_proc WHERE
prosrc = 'text_timestamp')

Now the following work:
SELECT CAST(text '' AS timestamp);
SELECT CAST(char '' AS timestamp);
SELECT CAST(varchar '' AS timestamp);

But this doesn't yet:
SELECT CAST('' AS timestamp);

I can probably get it working, but is it really a good idea to be
fiddling with the pg_cast table like that?

Thanks

James

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of James Harper
> Sent: Friday, 10 February 2006 20:23
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] implicit cast of empty string to timestamp
>
> Is there anything I can do to make postgres allow an implicit cast of
an
> empty string to a timestamp, so that a badly behaved application can
do:
>
> INSERT INTO SomeTable (timestampfield) VALUES ('')
>
> Where timestampfield is of type typestamp. ?
>
> From what I understand of the 'CREATE CAST' command, I can't just
create
> a cast that only kicks in on empty strings whilst leaving casts of
other
> strings as is...
>
> Thanks
>
> James
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org