Is it possible to perform multiple replacements in one function call?

If you want to replace multiple strings in one go, you can of course nest the REPLACE function, eg. like this:

SELECT REPLACE(REPLACE(REPLACE(foo, 'apple', 'fruit'), 'banana', 'fruit'), 'lettuce', 'vegetable')
FROM bar

If you have to do a lot of replacing, your code will become ugly and hard to read. Is there such a thing as a multi-replace function? Which would maybe take 2 arrays as arguments? To be sure, I’m familiar with the TRANSLATE function, but as my example indicates I want to replace whole words, not just single characters.

Answer

I would implement such a function in the following way:

CREATE OR REPLACE FUNCTION multi_replace(_string text, variadic _param_args text[])                                     
  RETURNS TEXT 
AS    
$BODY$    
DECLARE
  _index integer;
BEGIN
  FOR _index IN 1 .. cardinality(_param_args) - 1 by 2 loop
    _string := replace(_string, _param_args[_index], _param_args[_index+1]);
  end loop;
  RETURN _string;            
END;         
$BODY$
LANGUAGE plpgsql VOLATILE;

cardinality() returns the length of the parameter array, and by 2 increases the loop index by 2 for every iteration so that it’s safe to use _index and _index + 1 inside the loop to access the pairs that belong together.

Online example: https://rextester.com/LITG61720