2

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?

  • What does 10 different operations mean? on 10 different strings? Are all of your "replacements" just things you're getting rid of? or are some of them legit replacements? – Evan Carroll Jul 16 '17 at 5:26
  • Some are "rid of", others simple replacements. I am cleaning some addresses with lots of irregularities. – Randomize Jul 16 '17 at 6:03
  • Depending on how many you have and how long they are, you may consider compiling them into a single regex and doing it one pass with pl/perl – Evan Carroll Jul 16 '17 at 6:07
  • Actually some "look ahead" in the regex can help and perl regex supports it. I didn't know I could use pl/perl dialect – Randomize Jul 16 '17 at 6:10
1

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 once1:

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


1) 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.

|improve this answer|||||
2

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.

|improve this answer|||||
  • I really like your solution. Locally works nicely for me, but I have this problem now: dba.stackexchange.com/questions/179944/… – Randomize Jul 16 '17 at 10:51
  • @Randomize RDS isn't PostgreSQL. – Evan Carroll Jul 16 '17 at 16:45
  • it is not probably a "clean" PostgreSQL instance. What I am running at the moment is aws.amazon.com/rds/postgresql – Randomize Jul 16 '17 at 16:57
  • @EvanCarroll: Why the amazon-rds flag only? This is a valid question for PostgreSQL in general. And amazon-rds could also be well mysql. I think the question should be flagged with just postgresql, and its body be edited to include the fact that the user is limited to using only the extensions already provided by Amazon RDS PostgreSQL. – joanolo Jul 16 '17 at 19:10
  • I totally didn't realize Amazon-RDS was anything but a forked version of PostgreSQL – Evan Carroll Jul 16 '17 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.