我有一列数据存储在PostgreSQL数据库中，类型为TEXT。我需要对这些数据进行字符替换，将方括号替换成花括号。但是，我只想替换两层深度以内的方括号（包括主要的花括号）。由于这些字符串可能非常长，所以我认为使用正则表达式（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}
提前感谢您！
regexp_replace(
regexp_replace('str', E'\\[(([^][]|\\[([^][]|\\[[^][]*\\])*\\])*)\\]', E'{\\1}', 'g')
, E'\\[(([^][]|\\[([^][]|\\[[^][]*\\])*\\])*)\\]', E'{\\1}', 'g')
这个想法是通过两次匹配和替换来匹配并替换两个最外层的[]。
第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}
\[[^][]*\]（未转义）匹配 [...] 的一个实例
\[ 开始方括号[^][]* 零个或多个不是方括号的字符\] 结束方括号 [ 开头，以 ] 结尾，并表示0级的一个实例(没有被][分隔)，则第一个/内部的 regexp_replace 也可以通过替换 ^ 开始处的 [ 和 $ 结束处的 ] 来完成： E '^ \\[(.*) \\]$ '与 E '{\\ 1}' 进行替换。
为了在这里添加嵌套，这里有一个最多深度为4级的示例：
\[([^][]| # outer
\[([^][]| # lvl 1
\[([^][]| # lvl 2
\[([^][]| # lvl 3
\[[^][]*\] # lvl 4
)*\]
)*\]
)*\]
)*\]
将外部的[]中的内容包装到捕获组中，4级模式的模式将变为：
\[(([^][]|\[([^][]|\[([^][]|\[([^][]|\[[^][]*\])*\])*\])*\])*)\]
如果要与 regex_replace 一起使用，则可能需要对 [] 进行额外的转义。
\\[(([^][]|\\[([^][]|\\[([^][]|\\[([^][]|\\[[^][]*\\])*\\])*\\])*\\])*)\\]
这可以像第一种模式一样在两个步骤中使用，并替换为E'{\\1}'
这段代码虽然不太美观，但是它可以实现功能（并且避免了正则表达式复杂度的问题）。我希望我已经覆盖了所有可能出现的情况...
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;
仅供参考，这里提供了完全使用SQL的解决方案。为了符号清晰，它使用CTE，但是您也可以在FROM中使用子查询，没有递归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;
$$;
然而，它比其他解决方案慢。
replbracket在10000次迭代中需要950毫秒。消除CTEs并使用 unnest ... with ordinality可以将速度提高到约1400毫秒:
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)
$$;
如果您希望速度快，使用适当的过程式语言 - 如C语言。在PL/Python2中：
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));
}
运行时间：10,000次迭代的运行时间为8毫秒。非常好，它比原来快了250倍，而这还是在强制子查询的开销下实现的。