Thread: Extracting octets from an inet column

Extracting octets from an inet column

From
vessey@upei.ca
Date:
Hi,

I'm trying to find a method I can use to extract octets from an inet
value.  I have a column that contains host addresses, which are all
currently stored as "/32."  I'd like to be able to extract the third and
fourth octets of the address individually (due to the way we assign
IP addresses on our campus, this would be a useful thing to do).
This could be complete brain freeze, but I can't find a way to do it.

I've looked at trying to change the mask length from "/32" to "/24",
which would then let me get at the last octet using the host()
function.  I saw a reference to a set_masklen() function in 7.2, but
I'm using 7.1.2.  I looked at trying to convert a value to text,
manipulate the text, and then convert it back to an inet, but can't
find a magic combination to do this.

I also failed to find a way to access the raw byte values as
something like bit strings, which I could then play with.

Can anyone point me in the right direction?

Thanks!
Blair


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Blair Vessey                         VESSEY@UPEI.CA
Systems Manager, University of Prince Edward Island
Charlottetown, Prince Edward Island, Canada C1A 4P3
=-=  Voice: (902) 566-0388 Fax: (902) 566-0958  =-=
"An ounce of perception, a pound of obscure" - Rush


Re: Extracting octets from an inet column

From
Alex Pilosov
Date:
host() function doesn't do what you think it does.

Solution for your problem is munging data a bit in plperl (or pltcl or
plpgsql).


create function extract_octet(inet,int4) returns int4 as '
  my @x=split /\\./, shift;
  return $x[shift];
' language 'plperl';

On Sun, 15 Jul 2001 vessey@upei.ca wrote:

> Hi,
>
> I'm trying to find a method I can use to extract octets from an inet
> value.  I have a column that contains host addresses, which are all
> currently stored as "/32."  I'd like to be able to extract the third and
> fourth octets of the address individually (due to the way we assign
> IP addresses on our campus, this would be a useful thing to do).
> This could be complete brain freeze, but I can't find a way to do it.
>
> I've looked at trying to change the mask length from "/32" to "/24",
> which would then let me get at the last octet using the host()
> function.  I saw a reference to a set_masklen() function in 7.2, but
> I'm using 7.1.2.  I looked at trying to convert a value to text,
> manipulate the text, and then convert it back to an inet, but can't
> find a magic combination to do this.
>
> I also failed to find a way to access the raw byte values as
> something like bit strings, which I could then play with.
>
> Can anyone point me in the right direction?
>
> Thanks!
> Blair
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Blair Vessey                         VESSEY@UPEI.CA
> Systems Manager, University of Prince Edward Island
> Charlottetown, Prince Edward Island, Canada C1A 4P3
> =-=  Voice: (902) 566-0388 Fax: (902) 566-0958  =-=
> "An ounce of perception, a pound of obscure" - Rush
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>