Thread: Re: Testing castability of text to numeric
> > I am trying to create a function that will test if a given text value > may safely be cast to numeric, returning the numeric cast or null if > impossible. > > Is there a way to "catch" the "ERROR: Bad numeric input format" error? > > I ask that rather than about what the numeric format is for regex-style > testing because if this works I may want to use a similar method to > check if a given text value casts safely to other types, like date. > I don't know of any way to catch errors. And I can't see any way how to test castability via regex. Can you? The only way I can think of at the moment is writing a C function. Regards, Christoph
On Fri, 16 May 2003, Christoph Haller wrote: > > > > I am trying to create a function that will test if a given text value > > may safely be cast to numeric, returning the numeric cast or null if > > impossible. I think that progress is being made on having java as a serverside language (i.e. plpgjava). Stored procedures in java would be really handy in order to do staff like that, java is known for its "exceptional" exception handling! What language do you use to bould your applications?? > > > > Is there a way to "catch" the "ERROR: Bad numeric input format" > error? > > > > I ask that rather than about what the numeric format is for > regex-style > > testing because if this works I may want to use a similar method to > > check if a given text value casts safely to other types, like date. > > > I don't know of any way to catch errors. > And I can't see any way how to test castability via regex. Can you? > The only way I can think of at the moment is writing a C function. > > Regards, Christoph > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Try something like this:<br /><br /><tt>numeric_column = CASE<br /> WHEN trim( text_column, '0123456789' ) = '' THEN<br/> text_column::NUMERIC<br /> ELSE<br /> NULL<br /> END<br /><br /></tt>If your text_column also has blanks(leading or trailing), you may want to adapt the above slightly, but I think you get the idea.<br /><br /> I don'tknow how to do arbitrarily formatted dates.<br /><br /> -- Dean<br /><br /> Achilleus Mantzios wrote on 2003-05-16 07:57:<br/><blockquote cite="cite" class="cite" type="cite">On Fri, 16 May 2003, Christoph Haller wrote:<br /><br /> >><br /> > > I am trying to create a function that will test if a given text value<br /> > > may safelybe cast to numeric, returning the numeric cast or null if<br /> > > impossible.<br /><br /> I think that progressis being made on<br /> having java as a serverside language<br /> (i.e. plpgjava).<br /> Stored procedures in javawould be really handy in order<br /> to do staff like that, java is known for its<br /> "exceptional" exception handling!<br/><br /> What language do you use to bould your applications??<br /><br /> > ><br /> > > Is therea way to "catch" the "ERROR: Bad numeric input format"<br /> > error?<br /> > ><br /> > > I ask thatrather than about what the numeric format is for<br /> > regex-style<br /> > > testing because if this worksI may want to use a similar method to<br /> > > check if a given text value casts safely to other types, likedate.<br /> > ><br /> > I don't know of any way to catch errors.<br /> > And I can't see any way how to testcastability via regex. Can you?<br /> > The only way I can think of at the moment is writing a C function.<br /> ><br /> > Regards, Christoph<br /> > <br /> > <br /> > <br /> > ---------------------------(end of broadcast)---------------------------<br/> > TIP 4: Don't 'kill -9' the postmaster<br /> > <br /><br /> -- <br /> ==================================================================<br/> Achilleus Mantzios<br /> S/W Engineer<br /> IT dept<br/> Dynacom Tankers Mngmt<br /> Nikis 4, Glyfada<br /> Athens 16610<br /> Greece<br /> tel: +30-210-8981112<br />fax: +30-210-8981877<br /> email: achill@matrix.gatewaynet.com<br /> mantzios@softlab.ece.ntua.gr<br /><br/><br /> ---------------------------(end of broadcast)---------------------------<br /> TIP 4: Don't 'kill -9' the postmaster</blockquote>
Achilleus Mantzios wrote: > On Fri, 16 May 2003, Christoph Haller wrote: > > >>>I am trying to create a function that will test if a given text value >>>may safely be cast to numeric, returning the numeric cast or null if >>>impossible. >> > > I think that progress is being made on > having java as a serverside language > (i.e. plpgjava). > Stored procedures in java would be really handy in order > to do staff like that, java is known for its > "exceptional" exception handling! It doesn't matter how exceptional Java's exception handling is. PostgreSQL's exception handling is just like a black hole - once the gravity got it, you won't get your transaction back ever. The current effort on subtransactions might change that. But I'm not sure what performance impact or other side effects it will have to execute every single statement inside of a stored procedure as a separate subtransaction just to have catchable exceptions. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Tue, 20 May 2003, Jan Wieck wrote: > Achilleus Mantzios wrote: > > On Fri, 16 May 2003, Christoph Haller wrote: > > > > > >>>I am trying to create a function that will test if a given text value > >>>may safely be cast to numeric, returning the numeric cast or null if > >>>impossible. > >> > > > > I think that progress is being made on > > having java as a serverside language > > (i.e. plpgjava). > > Stored procedures in java would be really handy in order > > to do staff like that, java is known for its > > "exceptional" exception handling! I would say, why offload such tasks (the fellow pgsql'er talked about casting exceptions) to postgresql when you can do that better in the client side language. > > It doesn't matter how exceptional Java's exception handling is. > PostgreSQL's exception handling is just like a black hole - once the > gravity got it, you won't get your transaction back ever. > > The current effort on subtransactions might change that. But I'm not > sure what performance impact or other side effects it will have to > execute every single statement inside of a stored procedure as a > separate subtransaction just to have catchable exceptions. > > > Jan > > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Hi Folks, 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. Best, Randall On Friday, May 16, 2003, at 10:57 AM, Achilleus Mantzios wrote: > On Fri, 16 May 2003, Christoph Haller wrote: > >>> >>> I am trying to create a function that will test if a given text value >>> may safely be cast to numeric, returning the numeric cast or null if >>> impossible. > > I think that progress is being made on > having java as a serverside language > (i.e. plpgjava). > Stored procedures in java would be really handy in order > to do staff like that, java is known for its > "exceptional" exception handling! > > What language do you use to bould your applications?? > >>> >>> Is there a way to "catch" the "ERROR: Bad numeric input format" >> error? >>> >>> I ask that rather than about what the numeric format is for >> regex-style >>> testing because if this works I may want to use a similar method to >>> check if a given text value casts safely to other types, like date. >>> >> I don't know of any way to catch errors. >> And I can't see any way how to test castability via regex. Can you? >> The only way I can think of at the moment is writing a C function. >> >> Regards, Christoph >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > > -- > ================================================================== > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel: +30-210-8981112 > fax: +30-210-8981877 > email: achill@matrix.gatewaynet.com > mantzios@softlab.ece.ntua.gr > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
>
> 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
testto 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'tparse 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.