Re: Date Validation?

> Richard,
>
>> Out of curiosity Josh, why aren't you validating in PHP? - only takes
>> a
>> couple of lines there.
>
> We are. It's just that all other validation takes place inside the
> PL/pgSQL functions in order to keep all the business logic in the
> database. Having one form of validation in the interface (PHP) and the
> rest in the function can cause the user to go through multiple
> validation errors, which is especially frustrating if the second
> validation error is fatal. Example:
[snip]

Hmm - fair enough - PITA really.

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).

Looks like there are a lot of dependencies - my C is too rusty to figure
that out just by skimming. I'm guessing the validation is fiddly though -
PHP's date handling code is no shorter.

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)

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).

For the interested on the list:
The central problem seems to be that the error logging function elog() never
returns from an ERROR message and kills the whole transaction.
This makes sense since any code can call elog(ERROR,...) and not have to
worry about recovering from the error.
If PostgreSQL had been written using Java, there'd probably be try...catch
everywhere and it wouldn't be an issue (of course there might well be
performance problems as well as someone having to invent java 10 years
before Sun did ;-)

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

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)

- Richard Huxton