Paul Mendoza wrote:
> Sent: Saturday, September 08, 2007 12:53 AM
> To: pgsql-general@xxxxxxxxxxxxxx
> Subject: Regular expression on a string problem.
>
> 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$)/'
> CONTEXT: PL/pgSQL function "clean_name" line 17 at assignment
>
>
> Here is the function. p_text is a varchar that might have "PR" in it.
>
> DECLARE
> var_regex_pattern varchar;
>
>
> BEGIN
>
>
>
> -- If "PR" is not in the text variable.
> IF position('PR' in p_text) = 0 THEN
> var_regex_pattern = '/\./';
> RETURN substring(p_text from var_regex_pattern);
> END IF;
>
> IF position('PR' in p_text) > 0 THEN
> -- If "PR" is in the text variable.
> var_regex_pattern = '/(\.PR\.)|(\.PR$)/';
> RETURN substring(p_text from var_regex_pattern);
> END IF;
>
>
> END;
The warning message and the problem that you encounter are not
really related.
You get the warning because standard_conforming_strings is off
and you use a string literal with a backslash in it without
preceeding it with the string escape syntax token E.
Because standard_conforming_strings is off, the backslash is
interpreted a an escape character and consequently when you
write '/(\.PR\.)|(\.PR$)/', the string actually becomes
'/(.PR.)|(.PR$)/'. You need to have double backslashes to
represent single ones and you need to say
E'/(\\.PR\\.)|(\\.PR$)/' if you want to avoid the warning.
This probably causes the behaviour that surprises you.
To help you further, I'd have to know what you actually
want to achieve, i.e. which output you desire for which
input.
The whole function could be written as a single SQL statement
using the CASE construct.
Yours,
Laurenz Albe
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
