Search Postgresql Archives

Re: ERROR: value out of range: underflow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 26 February 2010 14:33, Raymond O'Donnell <rod@xxxxxx> wrote:
> On 26/02/2010 12:15, Thom Brown wrote:
>> On 26 February 2010 12:02, Anton Maksimenkov <anton200@xxxxxxxxx> wrote:
>>> Hi.
>>>
>>> I have a simple function.
>>> CREATE OR REPLACE FUNCTION myf_convert_phone18digits(
>>>  in_phone VARCHAR
>>> ) RETURNS BIGINT
>>> -- IMMUTABLE
>>> AS $$
>>> DECLARE
>>>  t_extent_len BIGINT;
>>>  t_phone_18 BIGINT;
>>>  t_multiplier BIGINT;
>>> BEGIN
>>>
>>>  IF in_phone IS NULL OR in_phone = '' THEN
>>>        RAISE EXCEPTION 'in_phone[%] IS NULL OR =''''!', in_phone;
>>>  END IF;
>>>
>>>  t_extent_len := 18 - length(in_phone);
>>>  t_multiplier := 10::BIGINT ^ t_extent_len::BIGINT; --<<< ERROR HERE
>>>  t_phone_18 := (in_phone)::BIGINT;
>>>  t_phone_18 := t_phone_18 * t_multiplier;
>>>
>>>  RETURN t_phone_18;
>>>
>>> END;
>>> $$ LANGUAGE plpgsql;
>>>
>>>
>>> When I try it in pgAdmin, first time it show me error:
>>> --
>>> ERROR:  value out of range: underflow
>>> CONTEXT:  PL/pgSQL function "myf_convert_phone18digits" line 12 at assignment
>>> --
>>> SECOND (and consequences) time it works and just show result 771506000000000000
>>>
>>> I get same problem when I try to call query from web (php).
>>> I can't do "second time" here, so web always fail with "ERROR:  value
>>> out of range: underflow"
>>>
>>> What's the problem? What can I do with it?
>>> --
>>> antonvm
>>>
>>
>> If t_extent_len is 19 or more, it will fail as it will exceed the
>> maximum range of bigint.
>
> I'm curious as to why it worked the second time, when invoked twice in a
> row from pgAdmin (assuming that it was called with the same argument -
> the OP didn't say, but I'd imagine that was the case).
>

I'm also curious to know why there's an underflow error instead of
overflow.  And in fact, even if a 19 digit phone number were passed
in, it would only result in a 10^-1 calculation, resulting in 0.1.

I think we need to know what value is being passed in to generate this error.

Thom

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux