PostgreSQL:regex用大括号替换第一级方括号

Question

我有一个类型TEXT的PostgreSQL列中的数据，我需要做一些字符替换。具体来说，我想用大括号代替方括号。问题是，我只想替换不超过两层深的括号，如果你包括主要的封闭括号。这些字符串可能很长，所以我认为正则表达式可能是最好的方法（regexp_replace函数），但我不擅长正则表达式。这里有一个这样的值的例子：

[0,0,0,[12,2],0,0,[12,[1,2,3]],12,0,[12,2,[2]],12,0,12,0,0]

因此，我希望此字符串更改为：

{0,0,0,{12,2},0,0,{12,[1,2,3]},12,0,{12,2,[2]},12,0,12,0,0}

提前谢谢！

Answer 1

只是为了好玩，这里有一个完全SQL的解决方案。它使用CTE以实现符号的清晰，但是您可以在INF中使用子查询，因为没有递归CTE的使用。

编辑：添加简化，更快SQL版本，版本在Pl/Python，版本在C.C型稍微快一点——大约快250倍。

create or replace function repl(text) 
returns text 
language sql
as $$
with 
chars(pos, ch) as (
    -- In PostgreSQL 9.4 this can be replaced with an UNNEST ... WITH ORDINALITY
    -- it turns the string into a list of chars accompanied by their position within
    -- the string.
    select row_number() OVER (), ch
    from regexp_split_to_table($1,'') ch
),
nesting(ch, pos, lvl) as (
    -- This query then determines how many levels of nesting of [s and ]s are
    -- in effect for each character.
    select ch, pos, 
        sum(case ch when '[' then 1 when ']' then -1 else 0 end) OVER (ORDER BY pos) 
        from chars
),
transformed(ch, pos) as (
    -- and this query transforms [s to {s or ]s to }s if the nesting
    -- level is appropriate. Note that we use one less level of nesting
    -- for closing brackets because the closing bracket it self has already
    -- reduced the nesting level.
    select 
      case
        when ch = '[' and lvl <= 2 then '{' 
        when ch = ']' and lvl <= 1 then '}' 
        else ch
      end,
      pos
    from nesting
)
-- Finally, reconstruct the new string from the (char, position) tuples
select 
  string_agg(ch, '' order by pos)
from transformed;
$$;

但是，它比其他解决方案慢。

• Johnny 5的regexp解决方案需要450毫秒才能完成10000次迭代
• wildplasser的replbracket需要950ms才能完成10000次迭代
• 此CTE解决方案需要2050毫秒才能完成10000次迭代

摆脱CTE并使用unnest。。。使用“有序性”可将其速度提高到约1400ms：

create or replace function repl(text) returns text language sql volatile as
$$
    select
      string_agg(ch, '' order by pos)
    from (
        select
          case
            when ch = '[' and sum(case ch when '[' then 1 when ']' then -1 else 0 end) OVER (ORDER BY pos) <= 2 then '{'
            when ch = ']' and sum(case ch when '[' then 1 when ']' then -1 else 0 end) OVER (ORDER BY pos) <= 1 then '}'
            else ch
          end,
          pos
        from unnest(regexp_split_to_array($1,'')) with ordinality as chars(ch, pos)
    ) as transformed(ch, pos)
$$;

如果您想要快速，请使用适当的过程语言-或PL/Python2中的C：

create or replace function replpy(instr text) returns text language plpythonu as $$
def pyrepl(instr):
    level=0
    for ch in instr:
        if ch == '[':
                level += 1
                if level <= 2:
                        yield '{'
                else:
                        yield '['
        elif ch == ']':
                if level <= 2:
                        yield '}'
                else:
                        yield ']'
                level -= 1
        else:
                yield ch

return ''.join(pyrepl(instr))
$$;

它需要160毫秒。

好吧，鞭打一匹死马，让我们用c来做。作为扩展的完整源代码在这里，但这里是. c文件：

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(replc);
Datum replc(PG_FUNCTION_ARGS);

PGDLLEXPORT Datum
replc(PG_FUNCTION_ARGS)
{
    /* Set `buf` to a palloc'd copy of the input string, deTOASTed if needed */
    char * const buf = text_to_cstring(PG_GETARG_TEXT_PP(0));
    char * ch = buf;
    int depth = 0;


    while (*ch != '\0')
    {
        switch (*ch)
        {
            case '[':
                depth++;
                if (depth <= 2)
                    *ch = '{';
                break;
            case ']':
                if (depth <= 2)
                    *ch = '}';
                depth--;
                break;
        }
        ch++;
    }
    if (depth != 0)
        ereport(WARNING,
                (errmsg("Opening and closing []s did not match, got %d extra [s", depth)));

    PG_RETURN_DATUM(CStringGetTextDatum(buf));
}

运行时间：8毫秒，用于10000次迭代。很好，它比原来快了250倍，这是强制子查询的开销造成的。

Answer 2

这是丑陋的，但它工作（并避免regexp复杂性；-）我希望我有所有的角落案例涵盖...

CREATE OR REPLACE FUNCTION replbracket( _source text ) returns text
AS $func$
DECLARE
        pos_end INTEGER;
        pos_begin INTEGER;
        level INTEGER;
        result text;
BEGIN
        result = '' ;
        level = 0;
LOOP
        pos_begin = position ( '[' IN _source );
        pos_end = position ( ']' IN _source );
        -- raise notice 'Source=% Result=% Begin = % End=%'
                -- ,_source, result, pos_begin, pos_end;

        if (pos_begin < 1 AND pos_end < 1) THEN EXIT ;
        elsif (pos_begin < 1 ) THEN pos_begin =  pos_end + 1 ;
        elsif (pos_end < 1 ) THEN pos_end =  pos_begin + 1 ;
        end if;
        if (pos_begin < pos_end) THEN
                result = result || LEFT(_source, pos_begin-1);
                level = level + 1;
                if (level <= 2) THEN result = result || '{'; else result = result || '['; end if;
                _source = SUBSTR(_source, pos_begin+1);
        ELSE
                result = result || LEFT(_source, pos_end-1);
                level  = level - 1;
                if (level < 2) THEN result = result || '}'; else result = result || ']'; end if;
                _source = SUBSTR(_source, pos_end+1);
        END IF;
END LOOP;
        result = result || _source ;
        return result;
END

$func$ LANGUAGE plpgsql;

Answer 3

这将是一个痛苦的正则表达式，因为在PostgreSQL风格中可能没有递归可用。

对于最多2级嵌套深度检查，如果以下双替换工作（不能测试它）

regexp_replace(
  regexp_replace('str', E'\\[(([^][]|\\[([^][]|\\[[^][]*\\])*\\])*)\\]', E'{\\1}', 'g')
, E'\\[(([^][]|\\[([^][]|\\[[^][]*\\])*\\])*)\\]', E'{\\1}', 'g')

其想法是在两次过程中匹配并替换最外层的[]。请参见regex101中的示例：

传递1：{0,0,0，[12,2]，0,0，[12，[1,2,3]，12,0，[12,2，[2]]，12,0,12,0,0}
传递2：{0,0,0，{12,2}，0,0，{12，[1,2,3]}，12,0，{12,2，[2]}，12,0,12,0,0}

\[[^][]*\]匹配一个实例的[...]

• \[/code>开口方括号

注意，如果字符串总是以[开始，以]结束，并且表示0级的一个实例（不被][分隔），第一个/内部regexp_replace也可以通过替换[at^start和]at$end：E'^\\[（.*）\\]$'withE'{\\1}'

要在这里添加嵌套，请使用最多4个深度级别的示例：

\[([^][]|    # outer
\[([^][]|    # lvl 1
\[([^][]|    # lvl 2
\[([^][]|    # lvl 3
\[[^][]*\]   # lvl 4
)*\]
)*\]
)*\]
)*\]

将外部[]中的内容包装到一个捕获组中，4个级别的模式将变为：

\[(([^][]|\[([^][]|\[([^][]|\[([^][]|\[[^][]*\])*\])*\])*\])*)\]

与regex\u replace一起使用时可能需要额外转义[]

\\[(([^][]|\\[([^][]|\\[([^][]|\\[([^][]|\\[[^][]*\\])*\\])*\\])*\\])*)\\]

这可以像两个过程中的第一个模式一样使用，并替换为E'{\\1}'