In-Reply-To: <4332C38E.5040807@brandywine.com>
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
	<4332C38E.5040807@brandywine.com>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
Content-Transfer-Encoding: 7bit
Cc: pgsql-sql@postgresql.org
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Re: Updating cidr column with network operator
Date: Thu, 22 Sep 2005 19:58:34 +0200
To: daryl@brandywine.com

Thank you for responding, Daryl,

Am 22.09.2005 um 16:45 schrieb Daryl Richter:

> Axel Rau wrote:
>> Hi SQLers,
>> I have a fk from address to network and try to update the foreign key 
>> column to point at the network, "it belongs to":
>> CREATE TABLE network (
>>   id      cidr     PRIMARY KEY     , -- 'PK, ,IPv4/6 Network address'
>> )
>> CREATE TABLE address (
>>   id         inet     PRIMARY KEY      , -- 'PK of IPv4/6 host 
>> address'
>>   network cidr     NOT NULL            -- 'FK to Network table'
>>                       REFERENCES network ON DELETE CASCADE ON UPDATE 
>> CASCADE
>> )
>> I tried (using the WHERE clause to eliminate the addresses were no 
>> corresponding net exists):
>
> But you can't insert a row in address w/o a valid network.id?  That's 
> what the fk ensures.
>
> Perhaps you could elaborate more?  Are you trying to *put* on the fk 
> and you currently have bad data?
The fk requires a corresponding row in network. But my update tries to 
reference the right network, that one where the ip address belongs to.
>
>> UPDATE address
>>     SET network = (SELECT N.id WHERE A.id << N.id)
>>     FROM address A, network N
>>     WHERE A.id << N.id;
>
> This also makes no sense.  For starters, << is "bitwise shift left" ...
I'm using 8.0.3 and there are some new operators related to inet and 
cidr data types.
On page 157, I found "<<" as address/network "is contained in" network.

Finding the net where an address belongs to works as:
	SELECT id FROM network WHERE inet '$p_ipSource' << id;

Axel

Axel Rau, Frankfurt, Germany                           +49-69-951418-0