Re: Testing castability of text to numeric

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