PostgreSQL: regex replaces first level square brackets with curly braces

Just for kicks, here's a solution entirely in SQL. It uses CTEs for innovative clarity, but you can use subqueries in FROM instead, there is no recursive use of CTEs.

Edit . Added a simplified, faster SQL version, a Pl / Python version, and a C version. C - slightly faster - about 250 times faster.

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

      

        
        
        
      

    

However, it is slower than other solutions.

• Johnny 5's regexp solution takes 450ms for 10,000 iterations.
• wildplasser replbracket
  
  takes 950ms for 10,000 iterations.
• This CTE solution takes 2050ms for 10,000 iterations.

Getting rid of the CTE and using it unnest ... with ordinality

speeds it up to about 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)
$$;

      

        
        
        
      

    

If you want fast, use the correct procedural language - or C. In 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))
$$;

      

        
        
        
      

    

takes 160 ms.

OK, whipping a dead horse, let's do it in C. The full source code as an extension is here , but here's the .c file:

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

      

        
        
        
      

    

Execution time: 8ms for 10,000 iterations. Good enough, it's 250x faster than the original, and that comes with the overhead of a forced subquery.