[SOLVED] IPv4 longest prefix match using Postgres

IPv4 longest prefix match using Postgres

Given an IP address 192.168.0.1, and a table with a column next_hop_subnet storing subnet IP addresses, do you see any problem with the following PostGRESQL logic, accuracy or performance-wise:

minDif := select min(abs(inet '192.168.0.1' -  next_hop_subnet::inet)) 
         from routing_table 
         where next_hop_subnet::inet >>= inet '192.168.0.1';

select * 
from routing_table 
where next_hop_subnet::inet >>= inet '192.168.0.1' 
      AND abs(inet '192.168.0.1' -  next_hop_subnet::inet) = minDif;

Since, there can be multiple equally good matches, I think there is no way but to do this in two steps. Any suggestions?

Solution

I would use the masklen(inet) function to order the answers, like:

SELECT * FROM routing_table
 WHERE next_hop_subnet::inet >>= inet '192.168.0.1'
 AND masklen(next_hop_subnet::inet) = (
     SELECT masklen(next_hop_subnet::inet) FROM routing_table
     WHERE next_hop_subnet::inet >>= inet '192.168.0.1')
     ORDER BY masklen(next_hop_subnet::inet) DESC
     LIMIT 1
 );

That way you get the longest matching prefix from your routing table.