postgresql - input date validation check calling to_date -

i'm using postgresql-9.1.6 on centos.

i have issue on to_date function like...

postgres=# select to_date('20130229','yyyymmdd');   to_date    ------------  2013-03-01 (1 row) 

so i'm trying add validation check out of range against incoming date.

postgres=# select to_date('20130229','yyyymmdd');

error: timestamp out of range

i found hint here didn't work , asked here. unfortunately, couldn't answer.

finally, reached conclustion. below formatting.c 9 lines marked + added.

datum to_date(pg_function_args) {         text       *date_txt = pg_getarg_text_p(0);         text       *fmt = pg_getarg_text_p(1);         dateadt         result;         struct pg_tm tm;         fsec_t          fsec;      +   int ndays[]={-1,31,28,31,30,31,30,31,31,30,31,30,31};      +   int last_day_of_month;          do_to_timestamp(date_txt, fmt, &tm, &fsec);      +   last_day_of_month = ndays[tm.tm_mon];     +   if (((tm.tm_year & 3) == 0 && ((tm.tm_year % 25) != 0 || (tm.tm_year & 15) == 0)) && tm.tm_mon == 2 )     +           last_day_of_month = ndays[tm.tm_mon] + 1;      +   if( tm.tm_mon > 12 || tm.tm_mon < 1 || tm.tm_mday > last_day_of_month || tm.tm_mday < 1 )     +           ereport(error,     +                           (errcode(errcode_datetime_value_out_of_range),     +                            errmsg("timestamp out of range")));          result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - postgres_epoch_jdate;          pg_return_dateadt(result); } 

although own formatting.c works well, i'm not sure works perfect. i'm worried unexpected results throwing error against valid date.

any advice appreciated.

must patching server code? when patch break working sql , need updated when update postgresql? bad idea because you'll in habit of expecting to_date work way custom to_date works , things go sideways when work unpatched postgresql. might want take account other people stuck working custom postgresql server too, how supposed know to_date isn't to_date modified version? otoh, perhaps you're doing job security.

why not write own to_date replacement function, called strict_to_date, conversion using to_date , simple:

string = to_char(to_date_result, 'yyyymmdd') 

comparison? if to_date , to_char round trip doesn't give original result can raise exception. you'd need decide strict_to_date(null) of course that's easy add.

consider simple results:

=> select to_date('20130229','yyyymmdd');   to_date    ------------  2013-03-01 (1 row)  => select to_char(to_date('20130229','yyyymmdd'), 'yyyymmdd');  to_char   ----------  20130301 (1 row) 

since '20130229' != '20130301' have exception. wrap in function , slip in little comment why you're doing , should happy.