Обсуждение: BUG #3506: to_number silently ignore characters

The following bug has been logged online:

Bug reference:      3506
Logged by:          Laurent Martelli
Email address:      lmartelli@seditel.fr
PostgreSQL version: 7.4,8.1
Operating system:   7.4->Linux, 8.1->Windows
Description:        to_number silently ignore characters
Details:

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.

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

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


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

   Ok. Now the function is OK....<br /><br /><span style="font-weight: bold;">CREATE OR REPLACE FUNCTION</span>
my_to_number(text,text)<span style="font-weight: bold;">RETURNS</span> numeric <span style="font-weight: bold;">AS
</span><br/>$$ <br />    <span style="font-weight: bold;">BEGIN</span>  <br />        <span style="font-weight:
bold;">IFNOT </span>textregexeq($1,'^[0-9]+$') <span style="font-weight: bold;">THEN</span>  <br />               
<spanstyle="font-weight: bold;">RAISE EXCEPTION</span> 'TEXT % is not a valid NUMBER',$1; <br />        <span
style="font-weight:bold;">END IF;</span><br />        <span style="font-weight: bold;">RETURN</span> to_number($1,$2);
<br/><span style="font-weight: bold;">    END;</span><br />$$ <span style="font-weight: bold;">LANGUAGE PLPGSQL
IMMUTABLE;<br/><br /></span>Tests...<br /><br />=# select my_to_number('123.0','999999');<br />ERRO:  TEXT 123.0 is not
avalid NUMBER<br />=# select my_to_number('1230','999999');<br /> my_to_number<br />--------------<br />        
1230<br/>(1 registro)<br /><br />Tempo: 0,734 ms<br /><br />-- <br />William Leite Araújo<br />DBA - QualiConsult <br
/>

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




--
William Leite Araújo
DBA - QUALICONSULT