Thread: Help creating a function

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

am  Wed, dem 15.08.2007, um 17:29:17 -0400 mailte Madison Kelly folgendes:
>   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. :)

create or replace function bigint2inet(IN i bigint, OUT n inet) as $$
declare a int;
        b int;
        c int;
        d int;
begin
        a := i/(256^3)::int;
        b := ((i-(256^3)*a)/(256^2))::int;
        c := ((i-(256^3)*a-(256^2)*b)/256)::int;
        d := (i-(256^3)*a-(256^2)*b-256*c)::int;
        n := (a||'.'||b||'.'||c||'.'||d)::inet;
        return;
end;
$$ language plpgsql immutable strict;


hope thats helps, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net