From oliveiros.cristina@marktest.pt Fri Dec  11 16:05:46 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2A28F6330A8
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 12:05:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 97796-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 16:05:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116])
	by mail.postgresql.org (Postfix) with ESMTP id CC526632CE7
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 12:05:37 -0400 (AST)
Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by
	jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail
	Service Version 5.5.2653.13)
	id 4R8T9CAL; Fri, 11 Dec 2009 16:05:35 -0000
Message-ID: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
From: "Oliveiros C," <oliveiros.cristina@marktest.pt>
To: <pgsql-sql@postgresql.org>
Subject: Is there any function to test for numeric ips?
Date: Fri, 11 Dec 2009 16:05:34 -0000
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0CC6_01CA7A7B.C53F8180"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5
	tests=BAYES_20=-0.74, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200912/37
X-Sequence-Number: 33475

This is a multi-part message in MIME format.

------=_NextPart_000_0CC6_01CA7A7B.C53F8180
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear All,

I have a table with host names and some happen to be numeric IPs.

I would like to be able to filter out the later.

Is there any function pre-defined in the system that can test a =
particular text type value to see if it is a numeric ip?

Something that returns true if applied to '192.168.1.1' but false if =
applied to 'videos.sapo.pt' ?

I considered NOT LIKE '%.%.%.%' but I'm affraid it will filter out host =
names like 'www.google.com.br'

I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not =
a numeric IP,=20
is there any simple and direct way to somewhat trap that error and =
convert it to a false value that can be used
in a WHERE clause?

This is not to be included in a program, I'm just interested in a query =
that I can do directly to the back end on a client program like pgAdmin.

I know very little ( nothing, actually...) about function creation in =
pgplsql, so, before investing in learning, I would like to know if there =
is any built in function that does the trick, so I can skip the =
(potential) learning curve.
So far, I've googled up and down but found none

Thanx in advance for your kind and valuable help

Best,
Oliveiros




------=_NextPart_000_0CC6_01CA7A7B.C53F8180
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Dear All,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have a table with host names and some =
happen to=20
be numeric IPs.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I would like to be able to filter out =
the=20
later.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Is there any function pre-defined in =
the system=20
that can test a particular text type value to see if it is a numeric=20
ip?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Something that returns true if applied =
to=20
'192.168.1.1' but false if applied to 'videos.sapo.pt' ?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I considered NOT LIKE '%.%.%.%' but I'm =
affraid it=20
will filter out host names like 'www.google.com.br'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I've realized that, for ex,&nbsp;inet =
'x.x.x.x'=20
will fail if the input is not a numeric IP, </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>is there any simple and direct&nbsp;way =
to somewhat=20
trap that error and convert it to a false value that can be =
used</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>in a WHERE clause?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>This is not to be included in a =
program, I'm just=20
interested in a query that I can do directly to the back end on a client =
program=20
like pgAdmin.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I know very little ( nothing, =
actually...) about=20
function creation in pgplsql, so, before investing in learning, I would =
like to=20
know if there is any built in function that does the trick, so I can =
skip the=20
(potential) learning curve.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>So far, I've googled up and down but =
found=20
none</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanx in advance for your kind and =
valuable=20
help</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Oliveiros</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0CC6_01CA7A7B.C53F8180--




From fhevia@ip-tel.com.ar Fri Dec  11 16:19:01 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 8BE7563237F
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 12:19:01 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 60867-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 16:18:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from pyxis.powervt.com.ar (unknown [190.11.152.66])
	by mail.postgresql.org (Postfix) with ESMTP id 9F17C6315D5
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 12:18:50 -0400 (AST)
Received: from pcfhevia ([186.124.123.2])
	by pyxis.powervt.com.ar (IPGroup Mail Server) with ASMTP id 1OT48953;
	Fri, 11 Dec 2009 13:18:43 -0300
From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'Oliveiros C,'" <oliveiros.cristina@marktest.pt>,
	<pgsql-sql@postgresql.org>
References: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
Subject: Re: Is there any function to test for numeric ips?
Date: Fri, 11 Dec 2009 13:20:03 -0300
Organization: IP-TEL
Message-ID: <D417D556031146289BCD9BA0F96F20DB@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
thread-index: Acp6e9GgFlceBlI8R9G0RypN/8nHqwAAO/Wg
In-Reply-To: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.157 tagged_above=-10 required=5 tests=AWL=0.242,
	BAYES_40=-0.185, RDNS_NONE=0.1
X-Spam-Level: 
X-Archive-Number: 200912/38
X-Sequence-Number: 33476

 

> -----Mensaje original-----
> De: de Oliveiros C,
> 
> Dear All,
>  
> I have a table with host names and some happen to be numeric IPs.
>  
> I would like to be able to filter out the later.
>  
> Is there any function pre-defined in the system that can test 
> a particular text type value to see if it is a numeric ip?
>  
> Something that returns true if applied to '192.168.1.1' but 
> false if applied to 'videos.sapo.pt' ?
>  
> I considered NOT LIKE '%.%.%.%' but I'm affraid it will 
> filter out host names like 'www.google.com.br'
>  
> I've realized that, for ex, inet 'x.x.x.x' will fail if the 
> input is not a numeric IP, is there any simple and direct way 
> to somewhat trap that error and convert it to a false value 
> that can be used in a WHERE clause?
>  

You could filter IP out with a regular expression:

select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'

Cheers,
Fernando.




From hari.fuchs@gmail.com Fri Dec  11 16:24:18 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id BEA2F632CE7
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 12:24:18 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 99845-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 16:24:11 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 01430632CD3
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 12:24:10 -0400 (AST)
Received: from list by lo.gmane.org with local (Exim 4.50) id 1NJ8Ho-00021y-RV
	for pgsql-sql@postgresql.org; Fri, 11 Dec 2009 17:24:08 +0100
Received: from srv.protecting.net ([212.126.218.242])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 17:24:08 +0100
Received: from hari.fuchs by srv.protecting.net with local (Gmexim 0.1
	(Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 17:24:08 +0100
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: Is there any function to test for numeric ips?
Date: Fri, 11 Dec 2009 17:23:47 +0100
Lines: 28
Message-ID: <pu3a3hiing.fsf@srv.protecting.net>
References: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
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.2 (gnu/linux)
Cancel-Lock: sha1:x+Mv3D3P4eY5G9c2EL5qiihGTbE=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200912/39
X-Sequence-Number: 33477

In article <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>,
"Oliveiros C," <oliveiros.cristina@marktest.pt> writes:

> Dear All,
> I have a table with host names and some happen to be numeric IPs.
 
> I would like to be able to filter out the later.
 
> Is there any function pre-defined in the system that can test a particular text
> type value to see if it is a numeric ip?
 
> Something that returns true if applied to '192.168.1.1' but false if applied to
> 'videos.sapo.pt' ?
 
> I considered NOT LIKE '%.%.%.%' but I'm affraid it will filter out host names
> like 'www.google.com.br'
 
> I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a
> numeric IP,
> is there any simple and direct way to somewhat trap that error and convert it
> to a false value that can be used
> in a WHERE clause?

You could use a regular expression match:

  SELECT host
  FROM t1
  WHERE host ~ '^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$'




From fhevia@ip-tel.com.ar Fri Dec  11 16:24:52 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id B7BE463337C
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 12:24:52 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 00462-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 16:24:45 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from pyxis.powervt.com.ar (unknown [190.11.152.66])
	by mail.postgresql.org (Postfix) with ESMTP id 1DAC06332EF
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 12:24:44 -0400 (AST)
Received: from pcfhevia ([186.124.123.2])
	by pyxis.powervt.com.ar (IPGroup Mail Server) with ASMTP id 1OT48953;
	Fri, 11 Dec 2009 13:24:39 -0300
From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'Fernando Hevia'" <fhevia@ip-tel.com.ar>,
	"'Oliveiros C,'" <oliveiros.cristina@marktest.pt>,
	<pgsql-sql@postgresql.org>
References: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
	<D417D556031146289BCD9BA0F96F20DB@iptel.com.ar>
Subject: Re: Is there any function to test for numeric ips?
Date: Fri, 11 Dec 2009 13:26:00 -0300
Organization: IP-TEL
Message-ID: <52EA9FD8B55A43D7B06F4B97DCC88DFE@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
thread-index: Acp6e9GgFlceBlI8R9G0RypN/8nHqwAAO/WgAABvK8A=
In-Reply-To: <D417D556031146289BCD9BA0F96F20DB@iptel.com.ar>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.101 tagged_above=-10 required=5
	tests=BAYES_50=0.001, RDNS_NONE=0.1
X-Spam-Level: 
X-Archive-Number: 200912/40
X-Sequence-Number: 33478

 

> 
> You could filter IP out with a regular expression:
> 
> select hostname as hosts_not_ip
> from table
> where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
> 

Oops, i missed something. Its:

select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'





From alvherre@commandprompt.com Fri Dec  11 16:29:03 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 9CA46632CE7
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 12:29:03 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 78879-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 16:28:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from spinlock.commandprompt.com (host-215.commandprompt.net
	[207.173.203.215])
	by mail.postgresql.org (Postfix) with ESMTP id 3558A63303D
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 12:28:51 -0400 (AST)
Received: from perhan.alvh.no-ip.org (190-95-19-31.bk17-dsl.surnet.cl
	[190.95.19.31])
	by spinlock.commandprompt.com (Postfix) with ESMTPSA id 9D23B348043;
	Fri, 11 Dec 2009 08:28:49 -0800 (PST)
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id 1F5896E36E; Fri, 11 Dec 2009 13:28:47 -0300 (CLST)
Date: Fri, 11 Dec 2009 13:28:47 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is there any function to test for numeric ips?
Message-ID: <20091211162847.GD30833@alvh.no-ip.org>
References: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.555 tagged_above=-10 required=5 tests=AWL=0.044,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200912/41
X-Sequence-Number: 33479

Oliveiros C, wrote:

> I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a numeric IP, 
> is there any simple and direct way to somewhat trap that error and convert it to a false value that can be used
> in a WHERE clause?

Yes, you can create a plpgsql function with an exception block; return
false inside the exception, true otherwise.  It looks something like

begin
  perform $1::inet;
  return true;
exception
  when invalid_something then
    return false
end;

I don't remember the exact syntax and the exception name but that should
get you started.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



From oliveiros.cristina@marktest.pt Fri Dec  11 17:40:15 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 52EC9632CD3
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 13:40:15 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 89942-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 17:40:03 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116])
	by mail.postgresql.org (Postfix) with ESMTP id 2BCE36329AA
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 13:40:03 -0400 (AST)
Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by
	jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail
	Service Version 5.5.2653.13)
	id 4R8T9NYR; Fri, 11 Dec 2009 17:40:01 -0000
Message-ID: <78D47C9DA237489C8622D26C75548FAA@marktestcr.marktest.pt>
From: "Oliveiros C," <oliveiros.cristina@marktest.pt>
To: "Alvaro Herrera" <alvherre@commandprompt.com>,
	<hari.fuchs@gmail.com>
Cc: <pgsql-sql@postgresql.org>,
	<fhevia@ip-tel.com.ar>
References: <E94D918326D044C69895D829038E31B2@marktestcr.marktest.pt>
	<20091211162847.GD30833@alvh.no-ip.org>
Subject: Re: Is there any function to test for numeric ips?
Date: Fri, 11 Dec 2009 17:40:00 -0000
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.385 tagged_above=-10 required=5 tests=AWL=0.354,
	BAYES_20=-0.74, STOX_REPLY_TYPE=0.001
X-Spam-Level: 
X-Archive-Number: 200912/42
X-Sequence-Number: 33480

Howdy, Alvaro, Fernando and Harald.

Thanks a lot for your prompt reply.

As I'm in a big hurry with this I used the regular expressions thing, it 
worked fine.

But your solution looks good as well, Alvaro, the problem is my poor 
knowledge on pgplsql

Definitely, I have to study in more depth  subjects like pgplsql not to 
mention regular expressions

Thanx a lot for your fast help,

Best,
Oliveiros

----- Original Message ----- 
From: "Alvaro Herrera" <alvherre@commandprompt.com>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, December 11, 2009 4:28 PM
Subject: Re: [SQL] Is there any function to test for numeric ips?


> Oliveiros C, wrote:
>
>> I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not 
>> a numeric IP,
>> is there any simple and direct way to somewhat trap that error and 
>> convert it to a false value that can be used
>> in a WHERE clause?
>
> Yes, you can create a plpgsql function with an exception block; return
> false inside the exception, true otherwise.  It looks something like
>
> begin
>  perform $1::inet;
>  return true;
> exception
>  when invalid_something then
>    return false
> end;
>
> I don't remember the exact syntax and the exception name but that should
> get you started.
>
> -- 
> Alvaro Herrera 
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql