Início / dba / Perguntas / 179926
Accepted
Randomize
Asked: 2017-07-16 08:14:23 +0800 CST

Melhor maneira de concatenar uma sequência de substituições de strings no Amazon RDS?

  • 772

Eu tenho uma concatenação de substituições de strings desta maneira:

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;

Atualmente conta com 10 operações diferentes e o número não para de crescer. Também tem o inconveniente de mudar de posição para transformações forçar a renomear variáveis.

Existe uma maneira melhor de gerenciar essa situação?

postgresql amazon-rds

2 respostas

  1. Com plperlueRegexp::Assemble

    Depende do comprimento da string e do comprimento da entrada. Você pode criar um regexp totalmente otimizado, usando Regexp::Assemble do Perl e executá-lo com plperlu.

    O código abaixo armazena em cache o regex para toda a sessão, portanto, as chamadas subsequentes devem ser rápidas.

    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;

    Agora você pode chamá-lo com,

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

    Alternativamente, você hardcore os regexes lá, mas se você for fazer isso, você também pode cortar Regexp::Assemblee colar um regex pré-compilado.

    Complperl

    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;

    Velocidade

    Para este exemplo, peguei os seguintes dados de amostra, que são 300.000 linhas

    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);

    Para comparação, a versão acima de @joanolo leva 8,04 segundos

    SELECT norm(x) FROM foo;

    Usando plperl com,

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

    Leva 5,0 segundos.

    • 2
  2. Best Answer

    Você pode se ajudar com uma replacementsmesa:

    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'
) ;

    Que você preencheria com quantas substituições você precisasse realizar:

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

    E então mude sua função para usá-la:

    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 ;

    E confira os resultados:

    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                                                      
:-------------------------- | :----------- | :------------------------------------------------- ------
você não gosta de diacríticos | você gosta | e o que você quiser, tente também remover vírgulas

    Isso não será tão rápido quanto codificar todas as alterações em sua função, mas permitirá maior flexibilidade.

    Como alternativa, você pode simplesmente alterar a estrutura de código da sua função e reutilizar a mesma variável mais de uma vez 1 :

    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 ;

    Isso é mais rápido e a melhor alternativa se o número de substituições for moderado.

    Você pode verificá-lo no dbfiddle aqui

    1) PLPGSQL não é uma linguagem funcional que obriga você a atribuir apenas uma vez a vals, as variáveis ​​podem ser sobrescritas quantas vezes forem necessárias. Se você pensar em termos de Scala , eles são vars, não vals. Em termos de Java, eles não são immutable.

    • 1

relate perguntas