From: | Steve Atkins <steve(at)blighty(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Maximum limit on int in plpgsql |
Date: | 2004-11-30 19:18:10 |
Message-ID: | 20041130191810.GA9868@gp.word-to-the-wise.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Morris N. Grajower | 2004-11-30 19:27:32 | Postgres Design |
Previous Message | Robert Fitzpatrick | 2004-11-30 19:11:11 | Dropping sequences |