主页 / dba / 问题 / 179926
Accepted
Randomize
Asked: 2017-07-16 08:14:23 +0800 CST

在 Amazon RDS 中连接一系列字符串替换的更好方法？

  • 772

我以这种方式连接了字符串替换：

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;

目前它有 10 个不同的操作，并且这个数字还在不断增长。此外，将位置更改为转换强制重命名变量也很不方便。

有没有更好的方法来处理这种情况？

postgresql amazon-rds

2 个回答

  1. 与plperlu和Regexp::Assemble

    这取决于字符串的长度和输入的长度。您可以使用Perl 的 Regexp::Assemble创建完全优化的正则表达式，并使用 plperlu 运行它。

    下面的代码缓存整个会话的正则表达式，因此后续调用应该很快。

    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;

    现在你可以调用它，

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

    或者，您可以在其中对正则表达式进行硬核处理，但如果您打算这样做，您也可以砍掉Regexp::Assemble，然后粘贴一个预编译的正则表达式。

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

    速度

    对于这个例子，我采用了以下 300,000 行的示例数据

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

    相比之下，@joanolo 的上述版本需要 8.04 秒

    SELECT norm(x) FROM foo;

    使用 plperl，

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

    需要 5.0 秒。

    • 2
  2. Best Answer

    你可以通过一张replacements桌子来帮助自己：

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

    您将填充您需要执行的尽可能多的替换：

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

    然后更改您的功能以使用它：

    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 ;

    并检查结果：

    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                                                      
:------------------------ | :------------ | :------------------------------------------------ ------
你不喜欢变音符号 | 你喜欢 | 以及您喜欢的任何尝试也删除逗号

    这不会像硬编码函数中的所有更改那样快，但会提供最高的灵活性。

    作为替代方案，您可以只更改函数的代码结构，并多次重复使用同一个变量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 ;

    如果替换数量适中，这会更快，并且是最好的选择。

    您可以在dbfiddle此处查看

    1) PLPGSQL 不是一种强制你只给vals 赋值一次的函数式语言，变量可以根据需要被覆盖多次。如果你用Scala的术语来思考，它们是vars，而不是vals。用 Java 术语来说，它们不是immutable.

    • 1

相关问题