implicit cast of empty string to timestamp

am 10.02.2006, um 20:22:57 +1100 mailte James Harper folgendes:

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:

test=# select * from t1;
x
-----

foo
(2 rows)

test=# select case when x='' then to_char(now(),'DD-MM-YYYY HH:MM:SS')
else x end from t1;
x
---------------------
10-02-2006 10:02:37
foo
(2 rows)

INSERT INTO SomeTable (timestampfield) VALUES ('')

You can't insert a empty string into a timestamp, IIRC.

test=# create table t2 (id int, ts timestamp);
CREATE TABLE
test=# insert into t2 values (1, '');
ERROR: invalid input syntax for type timestamp: ""

test=# insert into t2 values (1, NULL);
INSERT 0 1
test=# insert into t2 values (2, now());
INSERT 0 1
test=# select* from t2;
id | ts
----+----------------------------
1 |
2 | 2006-02-10 10:34:33.046152
(2 rows)

test=# select coalesce(ts, now()) from t2;
coalesce
-------------------------------
2006-02-10 10:35:03.426692+01
2006-02-10 10:34:33.046152+01
(2 rows)

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

am 10.02.2006, um 20:22:57 +1100 mailte James Harper folgendes:

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 ('')

You can't insert a empty string into a timestamp, IIRC.

No, and if someone tries I want to put a NULL in there.

I had hoped the following might work (syntax is from memory):

CREATE SCHEMA fnord;
SET search_path TO fnord;
CREATE FUNCTION fnord.timestamp (text) RETURNS timestamp LANGUAGE SQL AS
$$
SELECT pg_catalog.timestamp('20010101') $$;

SELECT CAST(text '20060101' AS timestamp);
SELECT CAST(text '' AS timestamp);

I had hoped that my 'timestamp' function would have overridden the
unqualified function call in the cast, but it was not to be :(

Curiously tho, \df didn't even acknowledge the 'timestamp' function in
the database, even though it was in the pg_proc's table.

Oh well.

James

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@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of James Harper
Sent: Friday, 10 February 2006 20:23
To: pgsql-general@postgresql.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)---------------------------

"James Harper" <james.harper@bendigoit.com.au> writes:

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

You can doubtless hack it to work if you slash-and-burn hard enough.
The question is why don't you fix your buggy application instead ...

regards, tom lane

Not my application :(

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, 11 February 2006 01:59
To: James Harper
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] implicit cast of empty string to timestamp

"James Harper" <james.harper@bendigoit.com.au> writes:

How much trouble am I going to get into by modifying the pg_cast

table