Обсуждение: Maximum limit on int in plpgsql

Hello,

I am passing the ip address as a varchar. Eg: 133.9.4.11

Later on i am splitting the ip address with the delimiter as '.'


ip1t:= split_part($1,'.',1); // returns a text value
ip2t:= split_part($1,'.',2); // returns a text value
ip3t:= split_part($1,'.',3);
ip4t:= split_part($1,'.',4);

Then i am type casting it into an integer

select into ip1 cast(ip1t as integer);
select into ip2 cast(ip2t as integer);
select into ip3 cast(ip3t as integer);
select into ip4 cast(ip4t as integer);

The i am taking the sum using this formula

out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4;

When i run the procedure i am getting following error
pg_atoi : Numerical result out of range

I tried all possible datatypes but still i am getting the same error.

Is it the problem of typecasting or the limits on datatype?

Thank you in advance,
deepthi

Hi,

Am Dienstag, den 30.11.2004, 02:29 -0800 schrieb deepthi@granwill.com:
> Hello,
>
> I am passing the ip address as a varchar. Eg: 133.9.4.11
>
> Later on i am splitting the ip address with the delimiter as '.'
>
>
> ip1t:= split_part($1,'.',1); // returns a text value
> ip2t:= split_part($1,'.',2); // returns a text value
> ip3t:= split_part($1,'.',3);
> ip4t:= split_part($1,'.',4);
>
> Then i am type casting it into an integer
>
> select into ip1 cast(ip1t as integer);
> select into ip2 cast(ip2t as integer);
> select into ip3 cast(ip3t as integer);
> select into ip4 cast(ip4t as integer);
>
> The i am taking the sum using this formula
>
> out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4;
>
> When i run the procedure i am getting following error
> pg_atoi : Numerical result out of range
>
> I tried all possible datatypes but still i am getting the same error.
>
> Is it the problem of typecasting or the limits on datatype?

integer means int4 iirc.
Which is 32 bit, but signed so you only have 2^31-1 as maxint
Did you try int8 here too?
btw. there are IP datatypes in PG as well. Otoh, they dont support
ip->int8 conversion. It depends on what are your plans once you
have that number.

Regards
Tino

deepthi@granwill.com wrote:
> The i am taking the sum using this formula
>
> out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4;
>
> When i run the procedure i am getting following error
> pg_atoi : Numerical result out of range
>
> I tried all possible datatypes but still i am getting the same error.
>
> Is it the problem of typecasting or the limits on datatype?

Type integer=int4 and is signed. IP addresses are unsigned. You'll need
to use an int8 to hold them.

--
   Richard Huxton
   Archonet Ltd

deepthi@granwill.com wrote:
> I have tried using the int8 also, even then i am having the problem.

Please CC the list.

The following works for me. I suspect your equivalent of "i" is an int4.

CREATE OR REPLACE FUNCTION test_int8(text) RETURNS int8 AS '
DECLARE
   i   int8;
   tot int8;
BEGIN
     i := CAST($1 AS int8);
     tot := (i * 256 * 256 * 256) + (i * 256 * 256) + (i * 256) + i;
     RETURN tot;
END;
' LANGUAGE plpgsql;

SELECT test_int8('255');

--
   Richard Huxton
   Archonet Ltd

On Tue, Nov 30, 2004 at 11:18:44AM +0000, Richard Huxton wrote:
> deepthi@granwill.com wrote:
> >The i am taking the sum using this formula
> >
> >out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4;
> >
> >When i run the procedure i am getting following error
> >pg_atoi : Numerical result out of range
> >
> >I tried all possible datatypes but still i am getting the same error.
> >
> >Is it the problem of typecasting or the limits on datatype?
>
> Type integer=int4 and is signed. IP addresses are unsigned. You'll need
> to use an int8 to hold them.

Or use a 2^31 bias and use a signed int4. These functions map between
dotted-quads and int4s in this way, to maintain ordering. (Not as convenient
as the inet or cidr types, or just cobbling together a simple ip type as
a C function, but sometimes you have to do the inelegant approach...)

create or replace function ip2int(text) returns int as '
DECLARE
  a int;
  b int;
  c int;
  d int;
BEGIN
  a := split_part($1, ''.'', 1);
  b := split_part($1, ''.'', 2);
  c := split_part($1, ''.'', 3);
  d := split_part($1, ''.'', 4);
  RETURN (a-128) * 16777216 + b * 65536 + c * 256 + d;
END;
' LANGUAGE plpgsql IMMUTABLE;

create or replace function int2ip(int) returns text as '
DECLARE
  a int;
  b int;
  c int;
  d int;
BEGIN
   a := (($1 >> 24) & 255) # 128;
   b := ($1 >> 16) & 255;
   c := ($1 >> 8) & 255;
   d := $1 & 255;
  RETURN to_char(a, ''FM999'') || ''.'' || to_char(b, ''FM999'') || ''.'' || to_char(c, ''FM999
'') || ''.'' || to_char(d, ''FM999'');
END;
' LANGUAGE plpgsql IMMUTABLE;

Cheers,
  Steve