From: Leif Biberg Kristensen <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Re: Passing function parameters to regexp_replace
Date: Sat, 17 Sep 2011 19:27:55 +0200
User-Agent: KMail/1.13.7 (Linux/2.6.39-gentoo-r3; KDE/4.6.5; x86_64; ; )
References: <201109171321.43309.leif@solumslekt.org>
	<201109171331.58663.leif@solumslekt.org>
	<m3fwjvt7wo.fsf@passepartout.tim-landscheidt.de>
In-Reply-To: <m3fwjvt7wo.fsf@passepartout.tim-landscheidt.de>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201109171927.56033.leif@solumslekt.org>

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