Обсуждение: Problem using IP functions

Hi,

I'm having trouble using the host() and netmask() functions within a select
query.  For some reason, the following query returns 1 row:

ipdb=> select id, subnet_number from subnet where subnet_number =
'216.46.13.0';id | subnet_number
----+---------------96 | 216.46.13.0
(1 row)

Yet, if I replace the ip string literal with host('216.46.13.0/24'), I get
no results.  ie:

ipdb=> select id, subnet_number from subnet where subnet_number =
host('216.46.13.0/24');
id | subnet_number
----+---------------
(0 rows)

Even though host('216.46.13.0/24') evaluates to 216.46.13.0

ipdb=> select host('216.46.13.0/24');   host
-------------216.46.13.0
(1 row)

The subnet_number column is a varchar(16) which I assume you can compare
with a text data type, which is what host() returns.  Just to be sure, I
tried casting everything to type text, but that didn't do the trick.

ipdb=> select texteq(host('216.46.13.0/24')::text, '216.46.13.0'::text);texteq
--------f
(1 row)

Any insight would be greatly appreciated.

Marc

"Marc Lamothe" <mlamothe@openface.ca> writes:
> The subnet_number column is a varchar(16) which I assume you can compare
> with a text data type, which is what host() returns.

Are you on a pre-7.1 Postgres release?  host() is buggy before 7.1 ---
it includes a trailing null in its output, which it shouldn't oughta
have done.  You can't see the null from outside the system, but it
manages to mess up text comparisons anyway.

BTW, you should consider using inet or cidr datatype for that column
rather than varchar...
        regards, tom lane

How to remove from this mailing list ?


> "Marc Lamothe" <mlamothe@openface.ca> writes:
> > The subnet_number column is a varchar(16) which I assume you can compare
> > with a text data type, which is what host() returns.
> 
> Are you on a pre-7.1 Postgres release?  host() is buggy before 7.1 ---
> it includes a trailing null in its output, which it shouldn't oughta
> have done.  You can't see the null from outside the system, but it
> manages to mess up text comparisons anyway.
> 
> BTW, you should consider using inet or cidr datatype for that column
> rather than varchar...
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 


-- 
Subhramanya Shiva, Programmer
Archean InfoTech pvt.Ltd.
Hyderabad, India
http://www.archeanit.com

Hi

Perhaps you have sent you commands in the subject-line.
All commands should be sent by putting them in the body of the message.

Best regards
Per-Olof Pettersson

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 2001-05-14, 06:56:48, shiva@archeanit.com ("Subhramanya Shiva") wrote 
regarding how to remove ?:


> How to remove from this mailing list ?


> > "Marc Lamothe" <mlamothe@openface.ca> writes:
> > > The subnet_number column is a varchar(16) which I assume you can compare
> > > with a text data type, which is what host() returns.
> >
> > Are you on a pre-7.1 Postgres release?  host() is buggy before 7.1 ---
> > it includes a trailing null in its output, which it shouldn't oughta
> > have done.  You can't see the null from outside the system, but it
> > manages to mess up text comparisons anyway.
> >
> > BTW, you should consider using inet or cidr datatype for that column
> > rather than varchar...
> >
> >                     regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >


> --
> Subhramanya Shiva, Programmer
> Archean InfoTech pvt.Ltd.
> Hyderabad, India
> http://www.archeanit.com



> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)