X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 465F2D8AE4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  3 Sep 2005 14:18:58 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 95150-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  3 Sep 2005 17:18:52 +0000 (GMT)
Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.198])
	by svr1.postgresql.org (Postfix) with ESMTP id EC115D8AD9
	for <pgsql-sql@postgresql.org>; Sat,  3 Sep 2005 14:18:50 -0300 (ADT)
Received: by zproxy.gmail.com with SMTP id 8so510265nzo
	for <pgsql-sql@postgresql.org>; Sat, 03 Sep 2005 10:18:51 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=RnuGNJP7SmFQamPyAamHy2UF8m18fbW+WTiWvGtFViIO45hO0Zq1SpE38BH1ZqcjjsF5RhCFmj7TJ1dqxGIP2yiu3se2OygI+KYRshTrxLd6nMry+37aaRKSSUeDqhsikmeUnqUJ/l+N6KH8OYodyA3kRar4kIfjwh9pdEeNOag=
Received: by 10.36.148.16 with SMTP id v16mr3470165nzd;
	Sat, 03 Sep 2005 10:18:51 -0700 (PDT)
Received: by 10.36.23.12 with HTTP; Sat, 3 Sep 2005 10:18:51 -0700 (PDT)
Message-ID: <758d5e7f050903101842197d38@mail.gmail.com>
Date: Sat, 3 Sep 2005 19:18:51 +0200
From: Dawid Kuroczko <qnex42@gmail.com>
To: Julian Scarfe <julian@avbrief.com>
Subject: Re: Negative lookbehind assertions in regexs
Cc: pgsql-sql@postgresql.org
In-Reply-To: <09d701c5b098$10afee50$0600a8c0@Wilbur>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_7248_22047711.1125767931312"
References: <001a01c5ac84$496fc750$1102a8c0@dream>
	<008901c5ac9b$31bb8b50$0600a8c0@Wilbur>
	<20050829162103.GA32443@wolff.to>
	<09d701c5b098$10afee50$0600a8c0@Wilbur>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.658 required=5 tests=[AWL=0.031,
	FROM_ENDS_IN_NUMS=0.516, HTML_40_50=0.086, HTML_MESSAGE=0.001,
	RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200509/24
X-Sequence-Number: 22571

------=_Part_7248_22047711.1125767931312
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 9/3/05, Julian Scarfe <julian@avbrief.com> wrote:
>=20
> >> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the
> >> regex.
>=20
> From: "Bruno Wolff III" <bruno@wolff.to>
>=20
> > Something like:
> > (^.?CD)|([^B]CD)|([^A]BCD)
>=20
> Thanks to Bruno, and to Dawid who replied offline. The above does the job
> nicely.


I intended to post Cc: to the list, but somehow I didn't (blame it on=20
computers,
I just assumed Cc is set ;)).

Anyway, when perl_re's are craved for, once could use PLperl for this:

CREATE OR REPLACE FUNCTION perl_re(v text, r text)
RETURNS boolean LANGUAGE plperl STRICT IMMUTABLE AS $$
my ($val, $re) =3D @_;
return ($val =3D~ m{$re}) ? 't' : 'f';
$$;

...though it should be noted that queries WHERE perl_re(col, '(?<!AB)CD')
will not use indexes. (unless there are functional indexes on that function=
,
but then you would need one index for each regex used).

Regards,
Dawid

------=_Part_7248_22047711.1125767931312
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 9/3/05, <b class=3D"gmail_sendername">Julian Scarfe</b> &lt;<a href=3D"m=
ailto:julian@avbrief.com">julian@avbrief.com</a>&gt; wrote:<div><span class=
=3D"gmail_quote"></span><blockquote class=3D"gmail_quote" style=3D"border-l=
eft: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:=
 1ex;">
&gt;&gt; I'd like a regex that matches 'CD' but not 'ABCD' in any part of t=
he<br>&gt;&gt; regex.<br><br>From: &quot;Bruno Wolff III&quot; &lt;<a href=
=3D"mailto:bruno@wolff.to">bruno@wolff.to</a>&gt;<br><br>&gt; Something lik=
e:
<br>&gt; (^.?CD)|([^B]CD)|([^A]BCD)<br><br>Thanks to Bruno, and to Dawid wh=
o replied offline.&nbsp;&nbsp;The above does the job<br>nicely.</blockquote=
><div><br>
I intended to post Cc: to the list, but somehow I didn't (blame it on compu=
ters,<br>
I just assumed Cc is set ;)).<br>
<br>
Anyway, when perl_re's are craved for, once could use PLperl for this:<br>
<span class=3D"q" id=3D"q_1061c86317914c46_2"><br>CREATE OR REPLACE FUNCTIO=
N perl_re(v text, r text)<br> &nbsp; &nbsp;RETURNS boolean LANGUAGE plperl =
STRICT IMMUTABLE AS $$<br> &nbsp; &nbsp; &nbsp; &nbsp;my ($val, $re) =3D @_=
;<br> &nbsp; &nbsp; &nbsp; &nbsp;return ($val =3D~ m{$re}) ? 't' : 'f';
<br> &nbsp; &nbsp;$$;</span><br>
</div></div><br>
...though it should be noted that queries WHERE perl_re(col, <span class=3D=
"q" id=3D"q_1061c86317914c46_2">'(?&lt;!AB)CD')<br>
will not use indexes. (unless there are functional indexes on that function=
,<br>
but then you would need one index for each regex used).<br>
<br>
&nbsp;&nbsp; Regards,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dawid<br>
</span>

------=_Part_7248_22047711.1125767931312--