On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote: > Leif Biberg Kristensen <l...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql