Thread: Date Validation?
Folks,
Pardon me for asking this again, but I received *no* responses last
week.
Question: Is there any function or method which will allow me, in SQL
or PL/pgSQL, to validate dates without raising an error for invalid
dates?
Expansion: I have a number of PL/pgSQL functions which must accept
dates as parameters from the PHP4 interface. There is, however, the
possibility that an invalid date will be passed, such as "2001-02-31"
due to the nature of HTML forms date selector.
What I would like to do is accept the date as a VARCHAR parameter, and
then validate it and convert it to a date inside the function if it
passes validation.
However, all validation methods I've tried, such as ISFINTTE and
TO_DATE produce SQL errors if the date is not valid; this causes the
function to error out and the user gets an unfriendly error message
which I have not control over. This is unacceptable. Obviously, one
answer is error-handling inside functions, but we won't get that until
7.2 as far as I know.
I could write a complicated string-parsing function to test the
validity of each part of the date, but that would a) lock the interface
into passing the dates in only one format, and b) be really complicated
for leap years and the millenium.
I am hoping that someone has already dealt with this issue in a way
that date validation can be trappable.
Thanks for your advice.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
On Fri, 13 Jul 2001, Josh Berkus wrote: > Folks, > > Pardon me for asking this again, but I received *no* responses last > week. > > Question: Is there any function or method which will allow me, in SQL > or PL/pgSQL, to validate dates without raising an error for invalid > dates? Not as far as I know off hand, although I guess you could duplicate the code for to_date to another function except changing the error handling (a bit of a mess, but would probably function in a pinch).
From: "Josh Berkus" <josh@agliodbs.com> > Folks, > > Pardon me for asking this again, but I received *no* responses last > week. > > Question: Is there any function or method which will allow me, in SQL > or PL/pgSQL, to validate dates without raising an error for invalid > dates? > > Expansion: I have a number of PL/pgSQL functions which must accept > dates as parameters from the PHP4 interface. There is, however, the > possibility that an invalid date will be passed, such as "2001-02-31" > due to the nature of HTML forms date selector. Out of curiosity Josh, why aren't you validating in PHP? - only takes a couple of lines there. - Richard Huxton
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: USER: '2001-06-31' PHP Validation: '2001-06-31 is not a valid date.' USER: '2001-06-30' PL/pgSQL FUNCTION: 'Your session has timed out. Please log back in.' USER: '%^#$&*&#!!!' See what I mean? Not a showstopper, but it mars our otherwise user-friendly validation formula. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
> 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
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
On Sun, 15 Jul 2001 06:40, Josh Berkus wrote: > 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! 'Tis a shame. I could teach you C if you want. Get the lessons from the URL in the .signature box below. > I'm one of those > horrible people who came to SQL & PHP from VB and MS Access rather than > from a C.S. degree. Not 'horrible', nobody who has 'seen the light' in any way can be described as "horrible". That description is reserved for those folks who revel in their intimacy with darkness. ( How you define "darkness" is over to you ) > Heck, my college degree is in sculpture. No wonder the aesthetic is so important to you. > > 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). Well here is how I deal with the problem. First you have to turn off the error reporting flag in the php.ini configuration file. That stops most of the geek-speak rubbish getting out to the client. Now, please find attached the .php files I am using in a project. I feel that the level of geek-speak in the solution is accepable, but if it isn't to you or your clients, then you could always replace the error message goming out of PostgreSQL to something more understandable to the "Common Man / Lay Person" by editing the PostgreSQL source and re-compiling it. ( not difficult ) Note that I have the database here configured to the European representation of dates and the wording of the error messages in the php file reflects this. Also note that I have forgotton to test for dates being in the past, and that it would be better if the error message was delivered in a pop-up html page or a JavaScript alert. Off the top of my head another untested idea for a solution to the problem is to do the data verification in JavaScript on the client machine. There is a JavaScript date.parse() function. Qualify for the $50 ? -- Sincerely etc., NAME Christopher SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . co . nzCNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -> Please refrain from using HTML or WORD attachments in e-mails to me <-