On May 19, 2010, at 10:32 AM, Scot Kreienkamp wrote:
> Hi everyone,
>
> I have a column of type CIDR in a table that I am querying that contains
> the values of 10/8, 10.1/16,10.1.28/24, and 10.1.28.95. I am trying to
> return only the one record that's most specific compared to the IP
> address I'm currently on as this is being done in a CGI script. If
> there's no specific match for the IP of the originating workstation then
> it should return the /24 if it's there, then the /16 if it's there, etc.
> I have never worked with the CIDR type, and a novice when it comes to
> SQL query language, so I have no idea how to approach this.
Something like this (untested):
select foo from table where foo >>= '10.1.28.14' order by masklen(foo) desc limit 1;
You likely want to look at http://pgfoundry.org/projects/ip4r/ as an alternative,
if the table is likely to grow beyond a few dozen rows. It's usefully indexable
for "contains" queries, unlike the native cidr type,
Cheers,
Steve