Re: [GENERAL] int to inet conversion
Tom Lane wrote: Anton Nikiforov [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
Tom Lane wrote: Anton Nikiforov [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
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. pgpcstlpgc6iQ.pgp Description: PGP signature
Re: [GENERAL] int to inet conversion
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 smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
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. pgpFjv4s9DaQo.pgp Description: PGP signature
Re: [GENERAL] int to inet conversion
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 smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] 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 smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
Anton Nikiforov [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
