Regular expression replace in PL/pgSQL

Question

If I have the following input (excluding quotes):

"The ancestral   territorial      imperatives of the    trumpeter swan"

How can I collapse all multiple spaces to a single space so that the input is transformed to:

"The ancestral territorial imperatives of the trumpeter swan"

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.

What is the best approach? I can't seem to find a regular-expression replace function for PL/pgSQL. There is a text_replace 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.

Answer 1

PostgreSQL has a REGEXP_REPLACE function that you should be able to use.

DECLARE
  result VARCHAR2(255);
BEGIN
  result := REGEXP_REPLACE(subject, $$ {2,}$$, $$ $$, 'g');
END;

Answer 2

You could use plperl, and use perl regular expressions for the purpose.

Upgrading is good, but plperl can let you scrape by a little longer.