title: Regular expression replace in PL/pgSQL
body: <p>If I have the following input (excluding quotes):</p>
  
  <pre>"The ancestral   territorial      imperatives of the    trumpeter swan"</pre>
  
  <p>How can I collapse all multiple spaces to a single space so that the input is transformed to:</p>
  
  <pre>"The ancestral territorial imperatives of the trumpeter swan"</pre>
  
  <p>This is going to be used in a trigger function on insert/update (which already trims leading/trailing spaces). Currently, it raises an exception if the input contains multiple adjacent spaces, but I would rather it simply transforms it into something valid before inserting.</p>
  
  <p>What is the best approach? I can't seem to find a regular-expression replace function for PL/pgSQL. There is a <code>text_replace</code> function, but this will only collapse at most two spaces down to one (meaning three consecutive spaces will collapse to two). Calling this function over and over is not ideal.</p>