From: jan zimmek <jan.zimmek@web.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: replace text occurrences loaded from table
Message-Id: <47098D72-27B8-4634-8010-066B4765FE91@web.de>
Date: Tue, 30 Oct 2012 12:45:19 +0100
To: pgsql-sql@postgresql.org
Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\))

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=