Thread: function exception
While writing a date validation function, I’m not sure which condition to check for in the following exception handler.
CREATE OR REPLACE FUNCTION IsValidDateTime(timestamp with time zone) RETURNS BOOLEAN AS $$
BEGIN
PERFORM CAST($1 AS TIMESTAMP WITH TIME ZONE);
RETURN TRUE;
EXCEPTION WHEN INVALID_DATETIME_FORMAT THEN
RETURN FALSE;
END
$$ LANGUAGE 'plpgsql';
If I call it this way: SELECT IsValidDateTime(‘2008-05-06 00:00:95’);
I would expect it to return FALSE but it returns:
ERROR: date/time field value out of range: "2008-05-06 00:00:95"
Is there either a specific error code to use or is there a generic catch all code? I didn’t see anything better listed at http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html
Thanks.
"Wright, George" <George.Wright@infimatic.com> writes: > I would expect it to return FALSE but it returns: > ERROR: date/time field value out of range: "2008-05-06 00:00:95" > Is there either a specific error code to use or is there a generic catch > all code? I didn't see anything better listed at > http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html Well, the way to debug this sort of thing is to look at the error code number. In psql you do it like this: regression=# \set VERBOSITY verbose regression=# select '2008-05-06 00:00:95'::timestamptz; ERROR: 22008: date/time field value out of range: "2008-05-06 00:00:95" LOCATION: DateTimeParseError, datetime.c:3112 and then a search in the appendix for 22008 finds 22008 DATETIME FIELD OVERFLOW regards, tom lane
Perfect! Thank you. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, May 06, 2008 10:45 AM To: Wright, George Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] function exception "Wright, George" <George.Wright@infimatic.com> writes: > I would expect it to return FALSE but it returns: > ERROR: date/time field value out of range: "2008-05-06 00:00:95" > Is there either a specific error code to use or is there a generic catch > all code? I didn't see anything better listed at > http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html Well, the way to debug this sort of thing is to look at the error code number. In psql you do it like this: regression=# \set VERBOSITY verbose regression=# select '2008-05-06 00:00:95'::timestamptz; ERROR: 22008: date/time field value out of range: "2008-05-06 00:00:95" LOCATION: DateTimeParseError, datetime.c:3112 and then a search in the appendix for 22008 finds 22008 DATETIME FIELD OVERFLOW regards, tom lane