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