pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding broken regex'es
8+ messages / 5 participants
[nested] [flat]
* Finding broken regex'es
@ 2007-10-02 20:23 Enrico Weigelt <weigelt@metux.de>
0 siblings, 2 replies; 8+ messages in thread
From: Enrico Weigelt @ 2007-10-02 20:23 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I'm looking for some way to find broken regex'es in some column
to kick them off. For now I'm regularily fetching all regexes
from an PHP script, try an preg_match() and so find the broken
ones to later remove them.
Is there any way to do this directly within the db ?
thx
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service - http://www.metux.de/
---------------------------------------------------------------------
Please visit the OpenSource QM Taskforce:
http://wiki.metux.de/public/OpenSource_QM_Taskforce
Patches / Fixes for a lot dozens of packages in dozens of versions:
http://patches.metux.de/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-02 21:36 Erik Jones <erik@myemma.com>
parent: Enrico Weigelt <weigelt@metux.de>
1 sibling, 1 reply; 8+ messages in thread
From: Erik Jones @ 2007-10-02 21:36 UTC (permalink / raw)
To: weigelt@metux.de; +Cc: pgsql-sql
On Oct 2, 2007, at 3:23 PM, Enrico Weigelt wrote:
>
> Hi folks,
>
>
> I'm looking for some way to find broken regex'es in some column
> to kick them off. For now I'm regularily fetching all regexes
> from an PHP script, try an preg_match() and so find the broken
> ones to later remove them.
>
> Is there any way to do this directly within the db ?
IIRC, if they're PERL compatible which it would seem from the php
function you're using, no. Postgres supports POSIX regexes but not
(right now anyway) PERL regexes.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-03 03:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-10-03 03:48 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
Erik Jones <erik@myemma.com> writes:
> IIRC, if they're PERL compatible which it would seem from the php
> function you're using, no. Postgres supports POSIX regexes but not
> (right now anyway) PERL regexes.
Actually what we support are Tcl (Henry Spencer) regexes, which are
about as powerful as Perl's but have a few minor incompatibilities.
Tcl and Perl regexes are both supersets of the POSIX requirements.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-03 05:43 Erik Jones <erik@myemma.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Jones @ 2007-10-03 05:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Oct 2, 2007, at 10:48 PM, Tom Lane wrote:
> Erik Jones <erik@myemma.com> writes:
>> IIRC, if they're PERL compatible which it would seem from the php
>> function you're using, no. Postgres supports POSIX regexes but not
>> (right now anyway) PERL regexes.
>
> Actually what we support are Tcl (Henry Spencer) regexes, which are
> about as powerful as Perl's but have a few minor incompatibilities.
> Tcl and Perl regexes are both supersets of the POSIX requirements.
>
Excellent point of information. I was really just trying to focus on
why the Postgres regex engine wouldn't be equivalent to the matching
functionality of php's preg_* functions. One example would be look
behind assertions. Btw, am I crazy or do I remember someone
mentioning that support for Perl regexes possibly being added to
Postgres in the future.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-03 06:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2007-10-03 06:03 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
Erik Jones <erik@myemma.com> writes:
> Btw, am I crazy or do I remember someone
> mentioning that support for Perl regexes possibly being added to
> Postgres in the future.
You can already get exact-Perl-behavior regexes by means of a plperl
wrapper function. I can't really see any plausible argument for us
supporting both behaviors natively --- IMHO the three regex flavors
supported by Spencer's library are already two too many.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-03 13:36 Dawid Kuroczko <qnex42@gmail.com>
parent: Enrico Weigelt <weigelt@metux.de>
1 sibling, 1 reply; 8+ messages in thread
From: Dawid Kuroczko @ 2007-10-03 13:36 UTC (permalink / raw)
To: weigelt@metux.de; +Cc: pgsql-sql
On 10/2/07, Enrico Weigelt <weigelt@metux.de> wrote:
>
> Hi folks,
>
>
> I'm looking for some way to find broken regex'es in some column
> to kick them off. For now I'm regularily fetching all regexes
> from an PHP script, try an preg_match() and so find the broken
> ones to later remove them.
>
> Is there any way to do this directly within the db ?
Of course. Exceptions is what You need!
CREATE FUNCTION regex_is_broken(r text) RETURNS boolean AS $$
BEGIN
PERFORM '' ~ r;
RETURN 'f';
EXCEPTION
WHEN INVALID_REGULAR_EXPRESSION THEN
RETURN 't';
END;
$$ LANGUAGE PLpgSQL STRICT IMMUTABLE;
...and then you could do something like:
DELETE FROM table WHERE regex_is_broken(rx_col);
You don't need PLpgSQL to prevent such invalid regexes in the
first place. You could use CHECK constraint for it:
CREATE TABLE rx_check (
rx text CHECK ('' ~ rx IN ('t','f'))
);
postgres=> INSERT INTO rx_check (rx) VALUES ('.*');
INSERT 0 1
Time: 13.660 ms
postgres=> INSERT INTO rx_check (rx) VALUES ('234234');
INSERT 0 1
Time: 2.282 ms
postgres=> INSERT INTO rx_check (rx) VALUES ('par).*');
ERROR: invalid regular expression: parentheses () not balanced
Regards,
Dawid
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-03 14:40 Filip Rembiałkowski <plk.zuber@gmail.com>
parent: Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Filip Rembiałkowski @ 2007-10-03 14:40 UTC (permalink / raw)
To: pgsql-sql
2007/10/3, Dawid Kuroczko <qnex42@gmail.com>:
> CREATE TABLE rx_check (
> rx text CHECK ('' ~ rx IN ('t','f'))
> );
wow. This is beautiful :)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Finding broken regex'es
@ 2007-10-03 15:41 Dawid Kuroczko <qnex42@gmail.com>
parent: Filip Rembiałkowski <plk.zuber@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Dawid Kuroczko @ 2007-10-03 15:41 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: pgsql-sql
On 10/3/07, Filip Rembiałkowski <plk.zuber@gmail.com> wrote:
> 2007/10/3, Dawid Kuroczko <qnex42@gmail.com>:
>
> > CREATE TABLE rx_check (
> > rx text CHECK ('' ~ rx IN ('t','f'))
> > );
>
> wow. This is beautiful :)
Personally I would wrap it around DOMAIN, i.e.:
CREATE DOMAIN regex AS
text CHECK ('' ~ VALUE IN (TRUE,FALSE));
And then use 'regex' type instead of 'text'. For a nice look&feel:
CREATE TABLE rx_test (
rx regex
);
qnex=>insert into rx_test values ('.*');
INSERT 0 1
qnex=>insert into rx_test values ('qwe');
INSERT 0 1
qnex=>insert into rx_test values ('aaa(aaa');
ERROR: invalid regular expression: parentheses () not balanced
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-10-03 15:41 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-02 20:23 Finding broken regex'es Enrico Weigelt <weigelt@metux.de>
2007-10-02 21:36 ` Erik Jones <erik@myemma.com>
2007-10-03 03:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-03 05:43 ` Erik Jones <erik@myemma.com>
2007-10-03 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-03 13:36 ` Dawid Kuroczko <qnex42@gmail.com>
2007-10-03 14:40 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2007-10-03 15:41 ` Dawid Kuroczko <qnex42@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox