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). Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@xxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
