Обсуждение: 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

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

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