Обсуждение: Help creating a function

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. :)

Thanks all!

Madi

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