Thread: int to inet conversion
Dear All.
is there any function that can translate INT to INET type?
the table contains ip and mask in different fields (int fields):
ip | integer | default 0
mask | integer | default -1
db=> select ip, mask from ips limit 2;
ip | mask
-------------+------
-1062726656 | -256
-1062724096 | -256
But i need inet or sidr records :)
Best regards,
Anton
Attachment
Anton Nikiforov <anton@nikiforov.ru> writes:
> is there any function that can translate INT to INET type?
Nothing built-in, and given the fact that "inet" no longer means "IPv4",
it's unlikely we'd add one in the future. But there's nothing stopping
you from adding one of your own. For example
regression=# create or replace function int2inet(int) returns inet as $$
regression$# declare oct1 int;
regression$# oct2 int;
regression$# oct3 int;
regression$# oct4 int;
regression$# begin
regression$# oct1 := ((($1 >> 24) % 256) + 256) % 256;
regression$# oct2 := ((($1 >> 16) % 256) + 256) % 256;
regression$# oct3 := ((($1 >> 8) % 256) + 256) % 256;
regression$# oct4 := ((($1 ) % 256) + 256) % 256;
regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4;
regression$# end$$ language plpgsql strict immutable;
CREATE FUNCTION
regression=# select int2inet(-1062726656);
int2inet
--------------
192.168.20.0
(1 row)
There's probably a better way to do the shifting-and-masking, but that
was the first thing that came to mind. (Actually, if you are planning
to push a whole lot of data through this, it might be worth your time
to write something in C. But for a one-shot data conversion task this
is probably plenty good enough.)
regards, tom lane
Tom Lane wrote: > Anton Nikiforov <anton@nikiforov.ru> writes: > >>is there any function that can translate INT to INET type? > > > Nothing built-in, and given the fact that "inet" no longer means "IPv4", > it's unlikely we'd add one in the future. But there's nothing stopping > you from adding one of your own. For example > > regression=# create or replace function int2inet(int) returns inet as $$ > regression$# declare oct1 int; > regression$# oct2 int; > regression$# oct3 int; > regression$# oct4 int; > regression$# begin > regression$# oct1 := ((($1 >> 24) % 256) + 256) % 256; > regression$# oct2 := ((($1 >> 16) % 256) + 256) % 256; > regression$# oct3 := ((($1 >> 8) % 256) + 256) % 256; > regression$# oct4 := ((($1 ) % 256) + 256) % 256; > regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4; > regression$# end$$ language plpgsql strict immutable; > CREATE FUNCTION > regression=# select int2inet(-1062726656); > int2inet > -------------- > 192.168.20.0 > (1 row) > > There's probably a better way to do the shifting-and-masking, but that > was the first thing that came to mind. (Actually, if you are planning > to push a whole lot of data through this, it might be worth your time > to write something in C. But for a one-shot data conversion task this > is probably plenty good enough.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > Thanks alot, Mr. Lane i was just thinking that there was something inside postgres to convert this types. But now will try to write this functions :) Best regards, Anton
Attachment
Tom Lane wrote: > Anton Nikiforov <anton@nikiforov.ru> writes: > >>is there any function that can translate INT to INET type? > > > Nothing built-in, and given the fact that "inet" no longer means "IPv4", > it's unlikely we'd add one in the future. But there's nothing stopping > you from adding one of your own. For example > > regression=# create or replace function int2inet(int) returns inet as $$ > regression$# declare oct1 int; > regression$# oct2 int; > regression$# oct3 int; > regression$# oct4 int; > regression$# begin > regression$# oct1 := ((($1 >> 24) % 256) + 256) % 256; > regression$# oct2 := ((($1 >> 16) % 256) + 256) % 256; > regression$# oct3 := ((($1 >> 8) % 256) + 256) % 256; > regression$# oct4 := ((($1 ) % 256) + 256) % 256; > regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4; > regression$# end$$ language plpgsql strict immutable; > CREATE FUNCTION > regression=# select int2inet(-1062726656); > int2inet > -------------- > 192.168.20.0 > (1 row) > > There's probably a better way to do the shifting-and-masking, but that > was the first thing that came to mind. (Actually, if you are planning > to push a whole lot of data through this, it might be worth your time > to write something in C. But for a one-shot data conversion task this > is probably plenty good enough.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > Sorry for my stupidity but, maybe there is a function that converts mask stored in int format to a numer of bits? ;) Your function easyly convert this mask to dot decimal notation, but how to count the number of 1 in it? Best regards, Anton
Attachment
On Sun, Dec 04, 2005 at 02:09:53PM +0300, Anton Nikiforov wrote: > Sorry for my stupidity but, maybe there is a function that converts mask > stored in int format to a numer of bits? ;) > Your function easyly convert this mask to dot decimal notation, but how > to count the number of 1 in it? No, but you can write one the same way like so: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t <> 0 and (t & i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout wrote: > On Sun, Dec 04, 2005 at 02:09:53PM +0300, Anton Nikiforov wrote: > >>Sorry for my stupidity but, maybe there is a function that converts mask >>stored in int format to a numer of bits? ;) >>Your function easyly convert this mask to dot decimal notation, but how >>to count the number of 1 in it? > > > No, but you can write one the same way like so: > > Let i be your input. > Calculate t = -i. > If i is in the right format, t will have exactly one bit set. > Test this with t <> 0 and (t & i) == t > If that's ok, then your answer is 32 - log2(t) > > Have a nice day, Sorry, did not quite catch. t in this case is int, and there is no log2(int) function..... Best regards, Anton
Attachment
On Sun, Dec 04, 2005 at 03:21:47PM +0300, Anton Nikiforov wrote: > Martijn van Oosterhout wrote: > >Let i be your input. > >Calculate t = -i. > >If i is in the right format, t will have exactly one bit set. > >Test this with t <> 0 and (t & i) == t > >If that's ok, then your answer is 32 - log2(t) > > > >Have a nice day, > Sorry, did not quite catch. > t in this case is int, and there is no log2(int) function..... But there is a log(x,y) function, so log(2,t) would work also. Note that 255.255.255.0 stored as integer is -256. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout wrote: > On Sun, Dec 04, 2005 at 03:21:47PM +0300, Anton Nikiforov wrote: > >>Martijn van Oosterhout wrote: >> >>>Let i be your input. >>>Calculate t = -i. >>>If i is in the right format, t will have exactly one bit set. >>>Test this with t <> 0 and (t & i) == t >>>If that's ok, then your answer is 32 - log2(t) >>> >>>Have a nice day, >> >>Sorry, did not quite catch. >>t in this case is int, and there is no log2(int) function..... > > > But there is a log(x,y) function, so log(2,t) would work also. Note > that 255.255.255.0 stored as integer is -256. > > Have a nice day, Thanks alot! Best regards, Anton