This is a multi-part message in MIME format.
------_=_NextPart_001_01C8AF84.D100D951
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
While writing a date validation function, I'm not sure which condition
to check for in the following exception handler.
=20
=20
CREATE OR REPLACE FUNCTION IsValidDateTime(timestamp with time zone)
RETURNS BOOLEAN AS $$
BEGIN
PERFORM CAST($1 AS TIMESTAMP WITH TIME ZONE);=20
RETURN TRUE;
EXCEPTION WHEN INVALID_DATETIME_FORMAT THEN
RETURN FALSE;
END
$$ LANGUAGE 'plpgsql';
=20
=20
If I call it this way: SELECT IsValidDateTime('2008-05-06 00:00:95');
=20
I would expect it to return FALSE but it returns:
ERROR: date/time field value out of range: "2008-05-06 00:00:95"
=20
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
=20
Thanks.
------_=_NextPart_001_01C8AF84.D100D951
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@[EMAIL PROTECTED]
Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>While writing a date validation function, =
I’m
not sure which condition to check for in the following exception =
handler.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>CREATE OR REPLACE FUNCTION =
IsValidDateTime(timestamp
with time zone) RETURNS BOOLEAN AS $$<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'> BEGIN<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'> PERFORM CAST($1 AS =
TIMESTAMP WITH TIME ZONE); <o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'> RETURN =
TRUE;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'> EXCEPTION WHEN INVALID_DATETIME_FORMAT =
THEN<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'> RETURN =
FALSE;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'> END<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>$$ LANGUAGE =
'plpgsql';<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>If I call it this way: SELECT =
IsValidDateTime(‘2008-05-06
00:00:95’); <o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>I would expect it to return FALSE but it =
returns:<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>ERROR: date/time field value out of =
range:
"2008-05-06 00:00:95"<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>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 <a
href=3D"http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html"=
>http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html</a><o:p=
></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'>Thanks.<o:p></o:p></span></font></p>
</div>
</body>
</html>
------_=_NextPart_001_01C8AF84.D100D951--
