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.zimmek@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 ?