BUG #3506: to_number silently ignore characters

Laurent Martelli wrote:

to_number('123.0','999999') returns 1230, at least on version 7.4 and 8.1. I
think it should return 123 or raise an error.

to_number will silently ignore any character that doesn't match the
pattern. That can be confusing, and not generally a very bright idea in
applications, but we can't really change the behavior because that could
break existing applications.

You can use the pattern '999999.' to get the behavior you want (returns
123).

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas a �crit :

Laurent Martelli wrote:

to_number('123.0','999999') returns 1230, at least on version 7.4 and 8.1. I
think it should return 123 or raise an error.

to_number will silently ignore any character that doesn't match the
pattern. That can be confusing, and not generally a very bright idea in
applications, but we can't really change the behavior because that could
break existing applications.

OK. I'm tempted to say that since this is not specified in the
documentation, applications should not rely on this behaviour. But I
understand you will not to break existing applications, so perhaps you
could add a function that would behave in a more expected manner, or at
least document precisely the current behaviour in the documentation.

Best regards,
Laurent

CREATE OR REPLACE my_to_number(text,text) RETURNS number AS
$$
BEGIN
IF NOT textregexeq($1,'[0-9]{'||length($2)||'}') THEN
RAISE EXCEPTION 'TEXT % is not a valid NUMBER',$1;
END IF;
RETURNS to_number($1,$2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

OR something like ...

2007/8/3, Laurent Martelli <laurent.martelli@seditel.fr>:

Heikki Linnakangas a écrit :

Laurent Martelli wrote:

to_number('123.0','999999') returns 1230, at least on version 7.4 and

8.1. I

think it should return 123 or raise an error.

to_number will silently ignore any character that doesn't match the
pattern. That can be confusing, and not generally a very bright idea in
applications, but we can't really change the behavior because that could
break existing applications.

OK. I'm tempted to say that since this is not specified in the
documentation, applications should not rely on this behaviour. But I
understand you will not to break existing applications, so perhaps you
could add a function that would behave in a more expected manner, or at
least document precisely the current behaviour in the documentation.

Best regards,
Laurent

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
William Leite Araújo
DBA - QUALICONSULT

2007/8/3, William Leite Araújo <william.bh@gmail.com>:

CREATE OR REPLACE my_to_number(text,text) RETURNS number AS

OOOps... CREATE OR REPLACE FUNCTION ...

$$

BEGIN
IF NOT textregexeq($1,'[0-9]{'||length($2)||'}') THEN
RAISE EXCEPTION 'TEXT % is not a valid NUMBER',$1;
END IF;
RETURNS to_number($1,$2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

OR something like ...

2007/8/3, Laurent Martelli < laurent.martelli@seditel.fr>:

Heikki Linnakangas a écrit :

Laurent Martelli wrote:

to_number('123.0','999999') returns 1230, at least on version 7.4 and

8.1. I

think it should return 123 or raise an error.

to_number will silently ignore any character that doesn't match the
pattern. That can be confusing, and not generally a very bright idea

in

applications, but we can't really change the behavior because that

could

break existing applications.

OK. I'm tempted to say that since this is not specified in the
documentation, applications should not rely on this behaviour. But I
understand you will not to break existing applications, so perhaps you
could add a function that would behave in a more expected manner, or at
least document precisely the current behaviour in the documentation.

Best regards,
Laurent

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
William Leite Araújo
DBA - QUALICONSULT

--
William Leite Araújo
Estudante de paternidade - 17a semana

Ok. Now the function is OK....

CREATE OR REPLACE FUNCTION my_to_number(text,text) RETURNS numeric AS
$$
BEGIN
IF NOT textregexeq($1,'^[0-9]+$') THEN
RAISE EXCEPTION 'TEXT % is not a valid NUMBER',$1;
END IF;
RETURN to_number($1,$2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

Tests...

=# select my_to_number('123.0','999999');
ERRO: TEXT 123.0 is not a valid NUMBER
=# select my_to_number('1230','999999');
my_to_number
--------------
1230
(1 registro)

Tempo: 0,734 ms

--
William Leite Araújo
DBA - QualiConsult