Regular expression replace in PL/pgSQL
- by dreamlax
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.
