Re: Regular expression on a string problem.

Paul Mendoza wrote:
> Here is a problem I'm having with a function I've created. It should be
> returning a varchar value no matter what the input is but I'm getting a null
> value back. Then it says in the error that I'm using "nonstandard use of
> escape in a string literal at character 9". What do you think this should be
> changed to?
>
> Query OK (0.45 sec)
> Return Value: NULL
> WARNING: nonstandard use of escape in a string literal at character 9
> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
> QUERY: SELECT '/(\.PR\.)|(\.PR$)/'

It's just the new (SQL-standard) string formatting rules.

Old-style PG strings allowed C-style backslash escapes (typically for
newlines etc, as the hint says). The SQL standard doesn't, so an
"escape" string format was introduced to allow the old behaviour along
with some config settings (standard_conforming_strings,
escape_string_warning, backslash_quote)

Now, you're not actually using the backslash as a C-escape, just as a
protector to the regexp. So, escape_string_warning is warning you
unnecessarily.

Now, none of that is why you're getting no results.

You're getting no results because you're using Perl-compatible regexps
and PostgreSQL is expecting POSIX-style*. See chapter 9.7.3 for details.

* - I keep doing this myself

--
Richard Huxton
Archonet Ltd