Better way to concatenate a sequence of string replacements in Amazon RDS? I have a concatenation of string replacements in this way:

CREATE OR REPLACE FUNCTION norm(t text) RETURNS text AS $$
        declare t1 text;
        declare t2 text;
        ...
        declare t10 text;
        BEGIN
            select replace (lower(t), ',', ' ') into t1;
            select regexp_replace (t1, '[^0-9a-z\s-]', '', 'g') into t2;
            ...
            select regexp_replace (t10, 'n?oise?.*', '', 'g') into t10;
            RETURN trim(t10);
        END;
$$ LANGUAGE plpgsql;


At the moment it has 10 different operations and the number keeps on grow. Also it has the inconvenient that changing position to transformations forces to rename variables.

Is there a better way to manage this situation?
With plperlu and Regexp::Assemble

It depends on the length of the string and the length of the input. You can create a fully optimized regexp, using Perl's Regexp::Assemble and run it with plperlu.

The below code caches the regex for the entire session, so subsequent calls should be stupid fast.

CREATE OR REPLACE FUNCTION replace_from_all(text, text[])
RETURNS TEXT
AS $$
  use strict;

  my ( $text, $rearray ) = @_;
  my $re;

  my $allre = join '', @$rearray;
  if ( exists $_SHARED{$allre} ) {
    $re = $_SHARED{$allre};
  }
  else {
    require "Regexp/Assemble.pm";
    my $ra = Regexp::Assemble-&gt;new;
    $ra-&gt;add($_) for @$rearray;
    $re = $_SHARED{$allre} = $ra-&gt;re;
  }

  $text =~ s/$re//ge;
  return $text;
$$
LANGUAGE plperlu
IMMUTABLE;


Now you can call it with, 

SELECT replace_from_all('foobar', ARRAY['o', 'b'] );


Alternatively, you hard core the regexes in there, but if you're going to do that you may as well chop out Regexp::Assemble, and just paste a precompiled regex.

With plperl

CREATE OR REPLACE FUNCTION replace_from_all(text, text[])
RETURNS TEXT
AS $$
  use strict;

  my ( $text, $rearray ) = @_;
  my $regexes;

  my $allre = join '', @$rearray;
  if ( exists $_SHARED{$allre} ) {
    $regexes = $_SHARED{$allre};
  }
  else {
    $regexes = $_SHARED{$allre} = [map qr/$_/, @$rearray];
  }

  foreach my $re ( @$regexes ) {
    $text =~ s/$re//ge;
  }
  return $text;
$$
LANGUAGE plperl
IMMUTABLE;


Speed

For this example I took the following sample data which is 300,000 rows

CREATE TABLE foo
AS
  SELECT t.x FROM generate_series(1,1e5)
  CROSS JOIN ( VALUES
    ('You don''t like diacriticals àèìòùáéíóú'),
    ('You do like noiseless numbers'),
    ('And whatever you like to try also, removing commas, , , ')
) AS t(x);


For comparison, the above version by @joanolo takes 8.04 seconds

SELECT norm(x) FROM foo;


Using plperl with,

SELECT replace_from_all(x, ARRAY['[^0-9a-z\s-]', 'n?oise?.*'])
FROM foo;


It takes 5.0 seconds.