Calculate masklen from netmask string in PostgreSQL

Question

I know this question has been asked before for different languages, but as far as I can see it relies on flow control ("for loops") and/or esoteric Perl functions.

So in PostgreSQL, I'm trying to write a database migration to convert our network information stored as text into proper inet types. Suppose my 'Foo' table has two columns, "address" which stores the IP address itself, and "netmask" which stores the "netmask" (e.g. '255.255.255.0'). I want to insert these into the single 'ip' field of a new 'Bar' table as a single inet-type column.

So far what I've got is pretty straightforward for converting the IP, but I'm struggling on the mask length. I assume it'll look something like:

INSERT INTO bar(ip)
    SELECT set_masklen(address::inet, calculate_masklen_from_netmask(netmask))
    FROM foo
;

... but I have no idea how to define that function calculate_masklen_from_netmask since I can't use flow control structures in a statement-based language.

Answer 1

You can (ab)use inet_merge for that purpose. Use masklen(inet_merge(netmask, inet '255.255.255.255').

In your case you may more cleanly get the full address using inet_merge(address & netmask, address | ~netmask) (but avoid using netmask as a name as it is already a function). You can omit & netmask, I it included for completeness/clarity. You could just do inet_merge(address, address | ~netmask)