From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | David Wheeler <david(at)kineticode(dot)com> |
Cc: | "Jon Asher" <jon(at)vagabond-software(dot)com>, sfpug(at)postgresql(dot)org |
Subject: | Re: Generic regex escape function? |
Date: | 2004-11-17 19:03:48 |
Message-ID: | 200411171103.48939.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
David,
> The...what? What is "tri-mode regex support"?
PostgreSQL has "Advanced", "Basic" and "Extended". Basic and extended are
Posix forms, and "Advanced" is most similar to Perl's regex (and is also the
default). You can set this through the regex_flavor GUC variable, or via
escape codes in the regex itself.
> And what if
> <some-user-string> has a single quote (') in it?
Then you need to use "quote_literal". Except that quote_literal also includes
the beginning an terminating ' on the string, which is not so useful. So I
wrote a little function called double_quote(text) which uses quote_literal
but strips off the 1st and last '.
So, a user-input-proof regex comparison would be:
some_column ~* ( '***=' || double_quote(user_input_var))
see:
http://www.postgresql.org/docs/7.4/static/functions-matching.html#FUNCTIONS-POSIX-TABLE
Of course, this makes me inclined to simply use ILIKE and leave regex out of
it ...
--
Josh Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2004-11-18 19:16:11 | Poor, bored? |
Previous Message | David Wheeler | 2004-11-17 18:38:44 | Re: Generic regex escape function? |