Message-ID: <43331359.1020703@brandywine.com>
Date: Thu, 22 Sep 2005 16:26:01 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
	<4332C38E.5040807@brandywine.com>
	<72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
In-Reply-To: <72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Axel Rau wrote:
> 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.

I'm still not understanding what you're trying to do, perhaps its a 
language issue. :)  Let me try again.

I built your schema and inserted some rows:

     insert into network( id ) values( '10.1' );

     insert into address( id, network ) values( '10.1.0.1', '10.1' );
     insert into address( id, network ) values( '10.1.0.2', '10.1' );
     insert into address( id, network ) values( '10.1.0.3', '10.1' );

I then select from network:

     id
     -----------
     10.1.0.0/16

and from address:

     id        network
     --------  -----------
     10.1.0.1  10.1.0.0/16
     10.1.0.2  10.1.0.0/16
     10.1.0.3  10.1.0.0/16

Why do you now want to update address.network?  They are already 
pointing to the right network, aren't they?

I think if you provide some sample data we can figure this out.

> 
>>
>>> 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;
> 

Ahh, ok.  see above.

> Axel
> 
> Axel Rau, Frankfurt, Germany                           +49-69-951418-0
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


-- 
Daryl