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
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 exampleregression=# 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
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 exampleregression=# 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
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/
Show quoted text
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.
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
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/
Show quoted text
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.
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