Re: Help creating a function

On Aug 16, 2007, at 9:35 AM, Madison Kelly wrote:

> Note: This is being sent again (in case it shows up later). It
> never seemed to have made it to the list.
>
> Hi all,
>
> I'm using ulogd with PostgreSQL which stores IP addresses as 32bit
> unsigned integers. So when I select some data I get something like:
>
> ulogd=> SELECT id, ip_saddr, ip_daddr, raw_pktlen, ip_totlen,
> tcp_window
> FROM ulog LIMIT 20;
> id | ip_saddr | ip_daddr | raw_pktlen | ip_totlen | tcp_window
> ----+------------+------------+------------+-----------+------------
> 1 | 3232235874 | 1074534522 | 46 | 46 | 25825
>
> Where 'ip_saddr' and 'ip_daddr' are 'bigint'. I know I can convert
> these numbers to dotted-decimal in perl with a small script like:
>
> -=-=-
> #!/usr/bin/perl
>
> # This would be the number read from the DB
> my $num=3232235874;
>
> # Now do the math
> my $temp=$num/256;
> my $D=256*($temp-int($temp));
> $temp=(int($temp))/256;
> my $C=256*($temp-int($temp));
> $temp=(int($temp))/256;
> my $B=256*($temp-int($temp));
> my $A=int($temp);
> my $ip="$A.$B.$C.$D";
>
> # Print the results
> print "'num': [$num] -> 'IP': [$ip]\n";
> -=-=-
>
> What I would like to do is create a function that would do the same
> thing so I could read out the IP addresses as standard dotted-decimal
> format. Could anyone help me with this? I am quite the n00b when it
> comes to functions. :)

These functions convert between signed 32 bit integers (with a -2^31
offset) and dotted quads. You should be able to tweak them pretty
easily:

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;

There's probably a neater way to do it via the inet (or ip4) data
types, but these functions should be easier to tweak to use bigint.

Cheers,
Steve