Обсуждение: ERROR: value out of range: underflow
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
On 26 February 2010 12:02, Anton Maksimenkov <anton200@gmail.com> 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. Are you sure you wish to perform calculations on a phone number? Why not pad it out? Like: select rpad('2329382',18,'0') which would return "232938200000000000" Regards Thom
On 26/02/2010 12:15, Thom Brown wrote: > On 26 February 2010 12:02, Anton Maksimenkov <anton200@gmail.com> 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). Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 26 February 2010 14:33, Raymond O'Donnell <rod@iol.ie> wrote: > On 26/02/2010 12:15, Thom Brown wrote: >> On 26 February 2010 12:02, Anton Maksimenkov <anton200@gmail.com> 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
Thom Brown escribió: > On 26 February 2010 14:33, Raymond O'Donnell <rod@iol.ie> wrote: > > On 26/02/2010 12:15, Thom Brown wrote: > >> On 26 February 2010 12:02, Anton Maksimenkov <anton200@gmail.com> wrote: > 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. If you pass a string longer than 342 chars it fails with an underflow. Not sure why it doesn't fail the second time, sounds fishy. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
2010/2/26 Anton Maksimenkov <anton200@gmail.com>: > 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 Oh, sorry to all. It was: SELECT myf_convert_phone18digits('7715060'); I still interesting why it fail first time but executed at second. 2010/2/26 Thom Brown <thombrown@gmail.com>: > Are you sure you wish to perform calculations on a phone number? Why > not pad it out? Like: select rpad('2329382',18,'0') which would > return "232938200000000000" Damn, right! I'll use it. -- antonvm