pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTesting castability of text to numeric
8+ messages / 5 participants
[nested] [flat]
* Testing castability of text to numeric
@ 2003-05-13 21:01 Randall Lucas <rlucas@tercent.net>
0 siblings, 0 replies; 8+ messages in thread
From: Randall Lucas @ 2003-05-13 21:01 UTC (permalink / raw)
To: pgsql-sql
Hi All,
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.
Best,
Randall
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
@ 2003-05-16 09:43 Christoph Haller <ch@rodos.fzk.de>
2003-05-16 14:57 ` Re: Testing castability of text to numeric Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-20 00:28 ` Re: Testing castability of text to numeric Dean Gibson (DB Administrator) <dba-sql@ultimeth.net>
0 siblings, 2 replies; 8+ messages in thread
From: Christoph Haller @ 2003-05-16 09:43 UTC (permalink / raw)
To: pgsql-sql; +Cc: rlucas@tercent.net
>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
2003-05-16 09:43 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
@ 2003-05-16 14:57 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-20 14:57 ` Re: Testing castability of text to numeric Jan Wieck <JanWieck@Yahoo.com>
2003-05-21 18:55 ` Re: Testing castability of text to numeric Randall Lucas <rlucas@tercent.net>
1 sibling, 2 replies; 8+ messages in thread
From: Achilleus Mantzios @ 2003-05-16 14:57 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql@postgresql.org, <rlucas@tercent.net>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
2003-05-16 09:43 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
2003-05-16 14:57 ` Re: Testing castability of text to numeric Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2003-05-20 14:57 ` Jan Wieck <JanWieck@Yahoo.com>
2003-05-21 13:55 ` Re: Testing castability of text to numeric Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jan Wieck @ 2003-05-20 14:57 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; rlucas@tercent.net
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 #
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
2003-05-16 09:43 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
2003-05-16 14:57 ` Re: Testing castability of text to numeric Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-20 14:57 ` Re: Testing castability of text to numeric Jan Wieck <JanWieck@Yahoo.com>
@ 2003-05-21 13:55 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleus Mantzios @ 2003-05-21 13:55 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; rlucas@tercent.net
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
2003-05-16 09:43 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
2003-05-16 14:57 ` Re: Testing castability of text to numeric Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2003-05-21 18:55 ` Randall Lucas <rlucas@tercent.net>
1 sibling, 0 replies; 8+ messages in thread
From: Randall Lucas @ 2003-05-21 18:55 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
2003-05-16 09:43 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
@ 2003-05-20 00:28 ` Dean Gibson (DB Administrator) <dba-sql@ultimeth.net>
1 sibling, 0 replies; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2003-05-20 00:28 UTC (permalink / raw)
To: pgsql-sql
Try something like this:
numeric_column = CASE
WHEN trim( text_column, '0123456789' ) = '' THEN
text_column::NUMERIC
ELSE
NULL
END
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.
I don't know how to do arbitrarily formatted dates.
-- Dean
Achilleus Mantzios wrote on 2003-05-16 07:57:
>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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Testing castability of text to numeric
@ 2003-05-22 12:51 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 2003-05-22 12:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: rlucas@tercent.net; mail@joeconway.com
>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-05-22 12:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-13 21:01 Testing castability of text to numeric Randall Lucas <rlucas@tercent.net>
2003-05-16 09:43 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
2003-05-16 14:57 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-20 14:57 ` Jan Wieck <JanWieck@Yahoo.com>
2003-05-21 13:55 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-21 18:55 ` Randall Lucas <rlucas@tercent.net>
2003-05-20 00:28 ` Dean Gibson (DB Administrator) <dba-sql@ultimeth.net>
2003-05-22 12:51 Re: Testing castability of text to numeric Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox