Re: Date Validation?

Richard,

> Poked around and had a think and can't come up with anything better
> than
> Stephan Szabo's suggestion of hacking PG's built-in function. You'll
> want
> to_timestamp() which is called from to_date and it's in
> src/backend/utils/adt/formatting.c - AFAICT you'll just need to
> comment out
> the elog(...) calls and return a null value (or whatever).

Not too likely from me ... I don't do C at all! I'm one of those
horrible people who came to SQL & PHP from VB and MS Access rather than
from a C.S. degree. Heck, my college degree is in sculpture.

> Failing that, what about writing is_valid_date() in pl-tcl/perl?
> Don't know
> anything about tcl, but should be easy enough in perl (cut & paste
> from a
> suitable module)

Sounds good. Anybody on the list I can beg (or pay $50) to write it and
post it to the list? I don't know from perl or tcl either (I do SQL,
PHP, Java and VB).

> Or (and this is horrible) check the date in PHP and if it's not valid
> replace it with NULL. You can check for the null in the trigger fn,
> but
> unfortunately you no longer have the original value for your error
> message
> (no, I don't like it either).

Hmmm ... but given the lack of alternatives, may be better than the
2-stage system ...

> Presumably, once we have nested transactions all this will be
> magically
> solved by wrapping the possibly dodgy statements with an inner
> transaction.

Yeah. I'm also assuming that when Jan and co. get full cursor support
and stored procedures worked out, error trapping will also become
available. Then this whole issue goes away with a simple ON EXCEPTION.

> Aside: I may have found the world's first "Y2K BC" bug - if we make
> enough
> noise over this it could turn the IT industry round again.
>
> richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date;
> ?column? | ?column?
> ---------------+---------------
> 2001-01-01 BC | 0001-01-01 BC
> (1 row)

Quick! Call the presses! Stop the computers!

I could also make a number of Christian theological jokes, but I don't
want to offend anyone on the list ....

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco