Multi Replace plpgsql

From PostgreSQL wiki

Jump to: navigation, search

Library Snippets

Replace multiple strings in a single pass

Works with PostgreSQL

Any version

Written in

PL/PgSQL

Depends on

Nothing

/* This function quotes characters that may be interpreted as special in a regular expression.
   It's used by the function below and declared separately for clarity. */
CREATE FUNCTION quote_meta(text) RETURNS text AS $$
  SELECT regexp_replace($1, '([\[\]\\\^\$\.\|\?\*\+\(\)])', '\\\1', 'g');
$$ LANGUAGE SQL strict immutable;

/* Substitute a set of substrings within a larger string.
   When several strings match, the longest wins.
   Similar to php's strtr(string $str, array $replace_pairs).
   Example:
   select multi_replace('foo and bar is not foobar',
             '{"bar":"foo", "foo":"bar", "foobar":"foobar"}'::jsonb);
   => 'bar and foo is not foobar'
 */
CREATE FUNCTION multi_replace(str text, substitutions jsonb)
RETURNS text
AS $$
DECLARE
 rx text;
 s_left text;
 s_tail text;
 res text:='';
BEGIN
 SELECT string_agg(quote_meta(term), '|' )
 FROM jsonb_object_keys(substitutions) AS x(term)
   WHERE term <> ''
 INTO rx;
 
 IF (COALESCE(rx, '') = '') THEN
   -- the loop on the RE can't work with an empty alternation
   RETURN str;
 END IF;
 
 rx := concat('^(.*?)(', rx, ')(.*)$'); -- match no more than 1 row   
 
 loop
   s_tail := str;
   SELECT 
       concat(matches[1], substitutions->>matches[2]),
       matches[3]
    FROM
      regexp_matches(str, rx, 'g') AS matches
    INTO s_left, str;
 
   exit WHEN s_left IS NULL;
   res := res || s_left;
 
 END loop;
 
 res := res || s_tail;
 RETURN res;
 
END 
$$ LANGUAGE plpgsql strict immutable;
Personal tools