X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id 8150F925747
	for <pgsql-sql@postgresql.org>; Thu, 22 May 2003 09:36:29 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193:10024])
	(amavisd-new) with ESMTP id 17345-05 for <pgsql-sql@postgresql.org>;
	Thu, 22 May 2003 09:35:58 -0400 (EDT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by developer.postgresql.org (Postfix) with SMTP id C4EB6926C32
	for <pgsql-sql@postgresql.org>; Thu, 22 May 2003 08:51:18 -0400 (EDT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Thu May 22 14:51:57 2003 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA16978; Thu, 22 May 2003 14:51:17 +0200 (METDST)
Message-ID: <3ECCC7C4.98619071@rodos.fzk.de>
Date: Thu, 22 May 2003 14:51:17 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: rlucas@tercent.net, mail@joeconway.com
Subject: Re: Testing castability of text to numeric
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/414
X-Sequence-Number: 13579

>
> I thought that I would point out that an answer that answers my
> original query (how to test for castability without throwing
exception)
> was posted to the pgsql-general list on Sunday, by Joe Conway.  He has

> an "str_validate" set of functions which he makes available at
> joeconway.com.  Thanks to Joe.
>
And I thought it's useful to point out that these
Limitations

  Currently the only supported data types are:
    - date
    - timestamp
    - interval

can easily be expanded for integer and float types using
the C code provided in ./src/backend/utils/adt/numutils.c

e.g. to test for integers

...
#include "postgres.h"

#include <errno.h> /* get declaration of errno */

#include "fmgr.h"
...
 switch (typeid)
 {
  case INT4OID:
   {
    long  l = 0;
    char    *badp = NULL;

    errno = 0;

    /*
     * Some versions of strtol treat the empty string as an error, but
     * some seem not to.  Make an explicit test to be sure we catch it.
     */

    if (str == (char *) NULL)
     PG_RETURN_BOOL(false);
    else if (*str == 0)
     PG_RETURN_BOOL(false);
    else
     l = strtol(str, &badp, 10);

    /*
     * strtol() normally only sets ERANGE. On some systems it also may
     * set EINVAL, which simply means it couldn't parse the input
string.
     * This is handled by the second "if" consistent across platforms.
     */
    if (errno && errno != EINVAL)
     PG_RETURN_BOOL(false);
    if (badp && *badp && *badp != 0)
     PG_RETURN_BOOL(false);

    /* must be OK */
    PG_RETURN_BOOL(true);
   }
   break;

  case DATEOID:
...

Regards, Christoph

PS I am not subscribed to the pgsql-general list, so may be someone who
is
would like to forward this. Thanks. And thanks to Joe for inspiration.