Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BF1169FC97C for ; Fri, 26 Oct 2007 08:44:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 84321-09 for ; Fri, 26 Oct 2007 08:44:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 8E1599FC965 for ; Fri, 26 Oct 2007 08:44:25 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1IlNby-0008PN-Hu for pgsql-sql@postgresql.org; Fri, 26 Oct 2007 11:44:22 +0000 Received: from srv.protecting.net ([212.126.218.242]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 26 Oct 2007 11:44:22 +0000 Received: from hf0217x by srv.protecting.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 26 Oct 2007 11:44:22 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: Harald Fuchs Subject: Re: JOINing based on whether an IP address is contained within a CIDR range? Date: Fri, 26 Oct 2007 13:44:14 +0200 Lines: 52 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: srv.protecting.net X-Archive: encrypt User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) Cancel-Lock: sha1:oaqcv/SAUELwsFeQl1RzXnRLtKM= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/132 X-Sequence-Number: 29504 In article , "Jamie Tufnell" writes: > Hi, > I am storing a log of HTTP requests in a database table (including IP address): > http_log: id(PK), path, time, ip > I have another table that contains CIDR ranges and names for them: > network_names: id(PK), cidr, name > Some example data for both tables: > network_names: > 1, 192.168.0.0/24, 'Engineering' > 2, 192.168.1.0/24, 'Media' > 3, 192.168.2.0/24, 'Engineering' > 4, 192.168.3.0/24, 'Accounting' > 5, 192.168.4.0/24, 'Engineering' > 6, 10.0.0.0/8, 'Engineering' > http_log: > 1, '/index.html', 110000001, 192.168.0.47/32 > 2, '/index.html', 110000023, 200.1.2.3/32 > 3, '/index.html', 110000059, 1.2.3.4/32 > 4, '/index.html', 110000232, 192.168.2.1/32 > 5, '/index.html', 113919102, 192.168.1.39/32 > 6, '/index.html', 129101293, 10.2.2.4/32 > 7, '/index.html', 132828282, 192.168.4.2/32 > Now, in trying to produce a report on this data, I've come up against an > interesting (to me at least!) problem.. > I basically want the same output as in http_log, but substituting the IP with > the network name where available, i.e: > 1, '/index.html', 110000001, Engineering > 2, '/index.html', 110000023, 200.1.2.3/32 > 3, '/index.html', 110000059, 1.2.3.4/32 > 4, '/index.html', 110000232, Engineering > 5, '/index.html', 113919102, Media > 6, '/index.html', 129101293, Engineering > 7, '/index.html', 132828282, Engineering > I'm wondering what the best way of doing this is (considering that http_log > could have >100000 rows) Is it possible to somehow JOIN using the <<= and >>= > network operators? There are PostgreSQL builtin functions for that, but I think they are unable to use indexes. I use http://pgfoundry.org/projects/ip4r/ and I think it's the best thing since the invention of sliced bread ;-)