Re: Date Validation? - Mailing list pgsql-sql
From | |
---|---|
Subject | Re: Date Validation? |
Date | |
Msg-id | 1239.192.168.1.16.995124597.squirrel@mainbox.archonet.com Whole thread Raw |
In response to | Re: Date Validation? ("Josh Berkus" <josh@agliodbs.com>) |
Responses |
Re: Date Validation?
("Josh Berkus" <josh@agliodbs.com>)
|
List | pgsql-sql |
> 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