How to speed up string cleanup function? I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced.

I am new to Postgres. My function ufn_cie_easy() performs way too slow:

DECLARE
  letter char = '';
  str_result TEXT = '';
  x integer;
  y integer;
  asc_code int;
BEGIN
  y:=1;
  x:=char_length(arg);
  LOOP
    letter=substring(arg from y for 1);
    asc_code=ascii(letter);
    IF (asc_code BETWEEN 47 and 58) or (asc_code BETWEEN 65 and 90) or (
        asc_code BETWEEN 97 and 122) THEN
      str_result := str_result || letter;
      ELSIF (asc_code BETWEEN 192 and 197) THEN
      str_result := str_result || 'A';
      ELSIF (asc_code BETWEEN 200 and 203) THEN
      str_result := str_result || 'E';
      ELSIF (asc_code BETWEEN 204 and 207) THEN
      str_result := str_result || 'I';
      ELSIF (asc_code BETWEEN 210 and 214) OR (asc_code=216) THEN
      str_result := str_result || 'O';
      ELSIF (asc_code BETWEEN 217 and 220) THEN
      str_result := str_result || 'U';
      ELSIF (asc_code BETWEEN 224 and 229) THEN
      str_result := str_result || 'a';
      ELSIF (asc_code BETWEEN 232 and 235) THEN
      str_result := str_result || 'e';
      ELSIF (asc_code BETWEEN 236 and 239) THEN
      str_result := str_result || 'i';
      ELSIF (asc_code BETWEEN 242 and 246) OR (asc_code=248) THEN
      str_result := str_result || 'o';
      ELSIF (asc_code BETWEEN 249 and 252) THEN
      str_result := str_result || 'u';
      ELSE
      CASE asc_code
        WHEN 352 THEN str_result := str_result || 'S';
        WHEN 338 THEN str_result := str_result || 'OE';
        WHEN 381 THEN str_result := str_result || 'Z';
        WHEN 353 THEN str_result := str_result || 's';
        WHEN 339 THEN str_result := str_result || 'oe';
        WHEN 382 THEN str_result := str_result || 'z';
        WHEN 162 THEN str_result := str_result || 'c';
        WHEN 198 THEN str_result := str_result || 'AE';
        WHEN 199 THEN str_result := str_result || 'C';
        WHEN 208 THEN str_result := str_result || 'D';
        WHEN 209 THEN str_result := str_result || 'N';
        WHEN 223 THEN str_result := str_result || 'ss';
        WHEN 230 THEN str_result := str_result || 'ae';
        WHEN 231 THEN str_result := str_result || 'c';
        WHEN 241 THEN str_result := str_result || 'n';
        WHEN 376 THEN str_result := str_result || 'Y';
        WHEN 221 THEN str_result := str_result || 'Y';
        WHEN 253 THEN str_result := str_result || 'y';
        WHEN 255 THEN str_result := str_result || 'y';
        ELSE str_result := str_result;
      END CASE;
      END IF;    
    y:=y+1;
    exit when  y=x+1;
  END LOOP;
  return str_result;
END;
The function should be noticeably faster with fewer assignments (none, actually, in this updated version):

CREATE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
BEGIN
RETURN replace(replace(replace(replace(replace(replace(
         translate($1,'ŠŽšžŸÝÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜàáâãäåçèéêëìíîïñòóôõöøùúûüýÿ!"#$%&amp;()*+,-./:;&lt;=&gt;?@[\]^_`{|}~€‚ƒ„…†‡ˆ‰‹‘’“”•–—˜™›¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×Þð÷þÐ'
                     ,'SZszYYAAAAAACEEEEIIIINOOOOOOUUUUaaaaaaceeeeiiiinoooooouuuuyy')
         ,'Œ','OE')
         ,'Æ','AE')
         ,'œ','oe')
         ,'æ','ae')
         ,'ß','ss')
         ,'''','');
END
$func$  LANGUAGE plpgsql;


Assignments are comparatively expensive in plpgsql.

But I suspect you really want to remove all accents (diacritic signs). Postgres provides the function unaccent() with the additional module unaccent:

CREATE OR REPLACE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
SELECT translate(unaccent(
        replace(replace(replace(replace(replace(
          $1
         ,'Œ','OE')
         ,'Æ','AE')
         ,'œ','oe')
         ,'æ','ae')
         ,'ß','ss')
       ), '!"#$%&amp;()*+,-./:;&lt;=&gt;?@[\]^_`{|}~€‚ƒ„…†‡ˆ‰‹‘’“”•–—˜™›¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×Þð÷þÐ''', '');
$func$  LANGUAGE sql IMMUTABLE;


Substantially faster, yet. And superior in every aspect if you actually want to remove all diacritic signs.

In addition to unaccent():


Include an escaped ' ('') in translate() directly.
Make it a simple SQL function.
Make the function IMMUTABLE.


Detailed explanation and more related issues to consider if you need an IMMUTABLE function with unaccent():


Does PostgreSQL support “accent insensitive” collations?


In Postgres 9.5 or older we need to expand ligatures like 'Œ' or 'ß' manually, since unaccent() always substitutes a single letter:

    SELECT unaccent('Œ Æ œ æ ß');

    unaccent
    ----------
    E A e a S


What I would do in Postgres 9.6

After this update to unaccent:


  Extend contrib/unaccent's standard unaccent.rules file to handle all
  diacritics known to Unicode, and expand ligatures correctly (Thomas
  Munro, Léonard Benedetti)


Bold emphasis mine. Using unaccent() like instructed in the linked answer above:

CREATE OR REPLACE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
SELECT trim(regexp_replace(regexp_replace(public.unaccent('public.unaccent', $1)
                                        , '[^a-zA-Z\d\s]', '', 'g')
                         , '\s+', ' ', 'g'));
$func$  LANGUAGE sql IMMUTABLE;


Almost, but not quite 100 % identical to what you had. Better IMHO:


Replace all accents and expands all ligatures with unaccent().
Remove all noise characters (everything except ASCII letters, digits and white space).
Fold whitespace to a single space (' ').
Trim leading and trailing spaces.