Обсуждение: Passing function parameters to regexp_replace

I'm trying to write a sql or plpgsql function update_nametags(TEXT, TEXT) 
which does a replace on this form:

UPDATE sources SET source_text = regexp_replace(source_text, 
E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like 
'%n="%$2%">%';

But I can't find out how to escape the parameters $1 and $2. I've also tried to 
wrap it in an EXECUTE and concatenate the parameters with no luck.

I'd also like to have the function return the number of affected rows.

regards, Leif

On Saturday 17. September 2011 13.21.43 Leif Biberg Kristensen wrote:

> UPDATE sources SET source_text = regexp_replace(source_text,
> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> '%n="%$2%">%';

Sorry, I pasted a literal replacement, and substituted the parameters by hand. 
The expression should of course be 

UPDATE sources SET source_text = regexp_replace(source_text, 
E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like 
'%n="%$1%">%'

regards, Leif

Leif Biberg Kristensen <leif@solumslekt.org> wrote:

>> UPDATE sources SET source_text = regexp_replace(source_text,
>> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
>> '%n="%$2%">%';

> Sorry, I pasted a literal replacement, and substituted the parameters by hand.
> The expression should of course be

> UPDATE sources SET source_text = regexp_replace(source_text,
> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> '%n="%$1%">%'

Try:

> UPDATE sources SET source_text = regexp_replace(source_text,
> CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g') where source_text like
> CONCAT('%n="%', $1, '%">%')

If $1 and $2 (can) include meta characters, you have to es-
cape them properly.
 Please consider that regexp_replace() uses POSIX Regular
Expressions while LIKE uses a different syntax. If possible,
I would replace the LIKE expression with its "~" equivalent
so chances of confusion are minimized.

Tim

On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote:
> Leif Biberg Kristensen <leif@solumslekt.org> wrote:
> 
> > UPDATE sources SET source_text = regexp_replace(source_text,
> > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> > '%n="%$1%">%'
> 
> Try:
> > UPDATE sources SET source_text = regexp_replace(source_text,
> > CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g')
> > where source_text like CONCAT('%n="%', $1, '%">%')

The function CONCAT doesn't exist i PostgreSQL. And I can't get it to work 
with EXECUTE and standard concatenation either:

pgslekt=> CREATE OR REPLACE FUNCTION update_nametags(TEXT, TEXT) RETURNS VOID 
AS $$
pgslekt$> BEGIN
pgslekt$>     EXECUTE $_$
pgslekt$>         UPDATE sources SET source_text =
pgslekt$>         REGEXP_REPLACE(
pgslekt$>             source_text,
pgslekt$>             E'n="(.*?)' || $1 || '(.*?)"',
pgslekt$>             E'n="\\1' || $2 || '\\2"', 'g'
pgslekt$>         )
pgslekt$>         WHERE source_text LIKE E'%n="%' || $1 || '%">%'
pgslekt$>     $_$;
pgslekt$> END
pgslekt$> $$ LANGUAGE PLPGSQL VOLATILE;
CREATE FUNCTION
Time: 1,105 ms
pgslekt=> select update_nametags('Brynild','Brynil');
WARNING:  nonstandard use of \\ in a string literal
LINE 6:             E'n="\\1' || $2 || '\\2"', 'g'                                      ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
QUERY:         UPDATE sources SET source_text =       REGEXP_REPLACE(           source_text,           E'n="(.*?)' ||
$1|| '(.*?)"',           E'n="\\1' || $2 || '\\2"', 'g'       )       WHERE source_text LIKE E'%n="%' || $1 || '%">%'

CONTEXT:  PL/pgSQL function "update_nametags" line 2 at EXECUTE statement
ERROR:  there is no parameter $1
LINE 5:             E'n="(.*?)' || $1 || '(.*?)"',                                  ^
QUERY:         UPDATE sources SET source_text =       REGEXP_REPLACE(           source_text,           E'n="(.*?)' ||
$1|| '(.*?)"',           E'n="\\1' || $2 || '\\2"', 'g'       )       WHERE source_text LIKE E'%n="%' || $1 || '%">%'

CONTEXT:  PL/pgSQL function "update_nametags" line 2 at EXECUTE statement
> If $1 and $2 (can) include meta characters, you have to es-
> cape them properly.
> 
>   Please consider that regexp_replace() uses POSIX Regular
> Expressions while LIKE uses a different syntax. If possible,
> I would replace the LIKE expression with its "~" equivalent
> so chances of confusion are minimized.

The intended use is to replace a short string like 'Jacob' with 'Jakob' within 
a specific XML attribute value.

regards, Leif

Leif Biberg Kristensen <leif@solumslekt.org> wrote:

> On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote:
>> Leif Biberg Kristensen <leif@solumslekt.org> wrote:

>> > UPDATE sources SET source_text = regexp_replace(source_text,
>> > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
>> > '%n="%$1%">%'

>> Try:
>> > UPDATE sources SET source_text = regexp_replace(source_text,
>> > CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g')
>> > where source_text like CONCAT('%n="%', $1, '%">%')

> The function CONCAT doesn't exist i PostgreSQL.

Eh, yes, of course.

>                                                 And I can't get it to work
> with EXECUTE and standard concatenation either:
> [...]

What do you need the EXECUTE for? Just drop it.

Tim