thanks igor, the combination of regexp_replace and string_agg works great for me.
the query runs a lot faster than my previous solution and is way easier to grasp. Anfang der weitergeleiteten Nachricht: > Von: Igor Romanchenko <igor.a.romanche...@gmail.com> > Betreff: Aw: [SQL] replace text occurrences loaded from table > Datum: 30. Oktober 2012 14:38:57 MEZ > An: jan zimmek <jan.zim...@web.de> > > Hello, > you can try somethig like > > UPDATE tmp_messages > SET message = regexp_replace( > message, > (SELECT string_agg(var,'|') FROM tmp_vars), > 'XXX', > 'g') > > The idea is to form a single replacement string and to do all the > replacements in one go. > 1 scan to form the replacement string (something like 'ABC|XYZ|VAR123'). > 1 scan and update do all the changes. > > On Tue, Oct 30, 2012 at 1:45 PM, jan zimmek <jan.zim...@web.de> wrote: > hello, > > i am actually trying to replace all occurences in a text column with some > value, but the occurrences to replace are defined in a table. this is a > simplified version of my schema: > > create temporary table tmp_vars as select var from > (values('ABC'),('XYZ'),('VAR123')) entries (var); > create temporary table tmp_messages as select message from (values('my ABC is > XYZ'),('the XYZ is very VAR123')) messages (message); > > select * from tmp_messages; > > my ABC is XYZ -- row 1 > the XYZ is very VAR123 -- row 2 > > now i need to somehow update the rows in tmp_messages, so that after the > update i get the following: > > select * from tmp_messages; > > my XXX is XXX -- row 1 > the XXX is very XXX -- row 2 > > i have implemented a solution in plpgsql by doing a nested for-loop over > tmp_vars and tmp_messages, but i would like to know if there is a more > efficient way to solve this problem ? > > > best regards > jan > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >