Better way to concatenate a sequence of string replacements in Amazon RDS?

Question

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?

Evan Carroll · Answer 1 · 2017-07-16T22:53:43.960

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->new;
    $ra->add($_) for @$rearray;
    $re = $_SHARED{$allre} = $ra->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.

joanolo · Accepted Answer · 2017-07-15T19:42:39.983

You can help yourself by having a replacements table:

CREATE TABLE replacements
(
    execution_order INTEGER NOT NULL PRIMARY KEY,
    search_regexp TEXT NOT NULL CHECK (search_regexp > ''),
    replace_by TEXT NOT NULL,
    flags TEXT NOT NULL DEFAULT 'g'
) ;

That you would populate with as many replacements you need to perform:

INSERT INTO replacements
    (execution_order, search_regexp, replace_by, flags)
VALUES
    (100, '[^0-9a-z\s-]', '', 'g'),
    (200, 'n?oise?.*', '', 'g') ;

And then change your function to use it:

CREATE OR REPLACE FUNCTION norm(t_in text) 
RETURNS text AS 

$body$
DECLARE
    t_out text ;
    rep record ;
BEGIN
    t_out := replace(lower(t_in), ',', ' ') ;
    FOR rep IN
        SELECT search_regexp, replace_by, flags
        FROM replacements
        ORDER BY execution_order
    LOOP
        t_out := regexp_replace(t_out, rep.search_regexp, rep.replace_by, rep.flags) ;
    END LOOP ;
    RETURN t_out ;
END ;
$body$ 

LANGUAGE plpgsql IMMUTABLE STRICT ;

And check the results:

SELECT
    norm('You don''t like diacriticals àèìòùáéíóú') AS n1,
    norm('You do like noiseless numbers') AS n2,
    norm('And whatever you like to try also, removing commas, , , ') AS n3;

n1                          | n2           | n3                                                      
:-------------------------- | :----------- | :-------------------------------------------------------
you dont like diacriticals  | you do like  | and whatever you like to try also  removing commas

This won't be as fast as hard-coding all the changes in your function, but will allow for highest flexibility.

As an alternative, you can just change the code structure of your function, and reuse the same variable more than once¹:

CREATE OR REPLACE FUNCTION norm(t_in text) 
RETURNS text AS 

$body$
DECLARE
    t_out text ;
BEGIN
    t_out := replace(lower(t_in), ',', ' ') ;
    t_out := regexp_replace(t_out, '[^0-9a-z\s-]', '', 'g') ;
    t_out := regexp_replace(t_out, 'n?oise?.*', '', 'g') ;
    -- Keep adding substitutions, or inserting them where appropriate
    RETURN t_out ;
END ;
$body$ 

LANGUAGE plpgsql IMMUTABLE STRICT ;

This is faster, and the best alternative if the number of substitutions is moderate.

You can check it at dbfiddle here

¹⁾ PLPGSQL is not a functional language that forces you to assign only once to vals, variables can be overwritten as many times as necessary. If you think in Scala terms, they're vars, not vals. In Java terms, they aren't immutable.

Better way to concatenate a sequence of string replacements in Amazon RDS?

2 Answers2

With plperlu and Regexp::Assemble

With plperl

Speed

With `plperlu` and `Regexp::Assemble`

With `plperl`