Remplacer une chaîne par une autre dans une chaîne plus large est simple en SQL, avec la fonction replace:

select replace('la valeur est bar', 'bar', 'foo');
     replace
-------------------
 la valeur est foo

Mais il n’y a pas dans les fonctions de base de PostgreSQL de fonction pour substituer des chaînes multiples, chacune par leur propre remplaçante, comme on peut trouver en PHP avec strtr, ou en Python ou Perl avec des opérateurs de substitution via expressions régulières (qu’on va utiliser plus loin dans ce billet justement).

En SQL, regexp_replace veut bien chercher une série de chaînes en une seule passe, mais pas les remplacer individuellement, c’est-à-dire que regexp_replace(string, 'foo|bar', 'baz', 'g') remplacerait foo et bar par baz, mais on ne peut pas lui demander de remplacer foo par une chaîne et bar par une autre.

Dans certaines situations, des appels successifs à replace peuvent convenir, et c’est cette solution qui semble émerger sur les réponses toutes faites trouvables sur le web, mais dans le cas général, cette méthode a un risque de produire des résultats faux par rapport à un remplacement en une seule passe.

Par exemple, dans la conversion d’un texte brut en entités HTML, ces cinq substitutions au minimum doivent être appliquées pour avoir du HTML valide:

 > → >
 < → &lt;
 & → &amp;
 " → &apos;
 ' → &quot;

Imaginons qu’on les applique dans cet ordre, avec une requête de ce genre:

select replace(replace(replace(replace(replace(
  rawtext,
  '>', '&gt;'),
  '<', '&lt;'),
  '&', '&amp;'),
  '"', '&apos;'),
  '''', '&quot;');

Si le texte à transformer est <strong> AT&T </strong>, le résultat produit sera:

&amp;lt;strong&amp;gt; AT&amp;T &amp;lt;/strong&amp;gt;

ce qui est clairement faux (si on le reconvertit en texte brut on ne retrouve pas l’original). Le bon résultat devrait être:

&lt;strong&gt; AT&amp;T &lt;/strong&gt;

Le problème avec la requête ci-dessus est qu’une fois que > a été remplacé par &gt; (ou < par &lt;), l’étape suivante de remplacement n’a aucun moyen de distinguer l’esperluète dans AT&T (qui doit être remplacée) de celle dans &gt; qui doit être laissée telle quelle.

Dans cet exemple en particulier, on peut réordonner les remplacements pour que & soit substitué par &amp; en premier. Mais dans le cas général ça peut être impossible à faire, s’il y a des références circulaires entre les chaînes à remplacer, par exemple si on veut inverser deux mots:

-- remplacer foo par bar et bar par foo.

-- mauvais résultat, version 1
select replace(replace('foo et bar', 'foo', 'bar'), 'bar', 'foo');

  replace   
------------
 foo et foo

-- mauvais résultat, version 2
 select replace(replace('foo et bar', 'bar', 'foo'), 'foo', 'bar');
 
  replace   
------------
 bar et bar

Pour le remplacement multiple, il faut un algorithme parcourant la chaîne et faisant les substitutions en une seule passe. Il faut aussi gérer le fait qu’il y ait parfois plusieurs substitutions possibles, avec une règle discriminante (souvent, le segment le plus long possible est remplacé, mais on peut choisir une autre règle si besoin).

On n’a donc pas cette fonction de base dans PostgreSQL, mais l’opérateur de substitution de Perl (s/pattern/replacement/flags) a cette fonctionnalité, puisque pattern peut être une suite d’alternatives et replacement peut être un tableau associatif (hash) clef/valeur avec toutes les substitutions, c’est-à-dire qu’on peut écrire ça:

my %subs = (foo=>bar, bar=>foo);
my $string = "foo et bar";
$string =~ s/(foo|bar)/$subs{$1}/g;
print $string;

En plus l’implémentation depuis Perl 5.10 de cette forme d’expression régulière a été spécifiquement optimisée pour être particulièrement efficace:

Trie optimisation of literal string alternations

Alternations, where possible, are optimised into more efficient matching structures. String literal alternations are merged into a trie and are matched simultaneously. This means that instead of O(N) time for matching N alternations at a given point, the new code performs in O(1) time. A new special variable, ${^RE_TRIE_MAXBUF}, has been added to fine-tune this optimization. (Yves Orton)

PostgreSQL permet d’écrire notre fonction en Perl via l’extension plperl, et en voici une version prête à l’emploi:

CREATE FUNCTION multi_replace(string text, orig text[], repl text[])
RETURNS text
AS $BODY$
  my ($string, $orig, $repl) = @_;
  my %subs;

  if (@$orig != @$repl) {
     elog(ERROR, "array sizes mismatch");
  }
  if (ref @$orig[0] eq 'ARRAY' || ref @$repl[0] eq 'ARRAY') {
     elog(ERROR, "array dimensions mismatch");
  }

  @subs{@$orig} = @$repl;
  
  my $re = join "|", map quotemeta,
     sort { (length($b) <=> length($a)) } keys %subs;
  $re = qr/($re)/;

  $string =~ s/$re/$subs{$1}/g;
  return $string;
$BODY$ language plperl strict immutable;

Cette fonction en Perl s’avère très rapide, même avec beaucoup de chaînes à remplacer (testé jusqu’à 1000, sachant qu’elles se cumulent dans une seule expression régulière) et beaucoup de remplacements effectifs.

On peut aussi l’écrire en plpgsql, mais le fait que l’ensemble des substitutions ne puisse pas se faire en une seule passe en SQL est très pénalisant dès qu’il y en a beaucoup à faire. Je ne mets pas le code ici, mais il est sur le wiki de postgresql.org: multi_replace en plpgsql. A part qu’il prend en entrée les remplacements via un paramètre de type jsonb plutôt que deux tableaux séparés, parce que c’est pratique et que ça permet de faire coexister les fonctions sous le même nom, le résultat produit doit être identique.

Par exemple, cet appel inversera foo et bar et en plus laissera foobar inchangé en utilisant l’astuce de le remplacer par lui même:

 select multi_replace(                                                           
 'foo et bar ne sont pas foobar',
 '{foo,bar,foobar}',
 '{bar,foo,foobar}');
         multi_replace         
-------------------------------
 bar et foo ne sont pas foobar

Que ce soit en plperl ou plpgsql, cette fonction évite le problème des replace() imbriqués avec leurs remplacements qui se chevauchent, et elle permet indifférement une liste de substitutions fixe ou dynamique. Mais si vous en avez besoin sur des chaînes de grande taille avec potentiellement beaucoup de substitutions, c’est un cas où Perl est vraiment beaucoup plus efficace que l’équivalent avec les fonctions SQL de base.