Re: Generic regex escape function?

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