Re: Maximum limit on int in plpgsql

On Tue, Nov 30, 2004 at 11:18:44AM +0000, Richard Huxton wrote:
> deepthi(at)granwill(dot)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