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
