regexp_replace - Mailing list pgsql-patches

This patch provides a new function regexp_replace.
regexp_replace extends a replace function and enables text search
by the regular expression. And, a back reference can be used within
a replace string.
(This patch for PostgreSQL 7.4.3)

Function: regexp_replace(str, pattern, replace_str)
Retuen Type: text
Description: Replace all matched string in str.
             pattern is regular expression pattern.
             replace_str is replace string that can use '\1' - '\9', and
'\&'.
             '\1' - '\9' is back reference to the n'th subexpression.
             '\&' is matched string.

(example1)
select regexp_replace('ABC-DEF', '(\\w+)-(\\w+)', '\\2-\\1')
result: DEF-ABC

(example2)
update tab1 set col1 = regexp_replace(col1, '[A-Z]', '');

---
Atsushi Ogawa
a_ogawa@hi-ho.ne.jp

--- cut here ---

*** ./src/backend/regex/regexec.c.orig    Tue Jul 20 08:45:39 2004
--- ./src/backend/regex/regexec.c    Tue Jul 20 08:49:36 2004
***************
*** 110,115 ****
--- 110,116 ----
      regmatch_t *pmatch;
      rm_detail_t *details;
      chr           *start;            /* start of string */
+     chr           *search_start;    /* search start of string */
      chr           *stop;            /* just past end of string */
      int            err;            /* error code if any (0 none) */
      regoff_t   *mem;            /* memory vector for backtracking */
***************
*** 168,173 ****
--- 169,175 ----
  pg_regexec(regex_t *re,
             const chr *string,
             size_t len,
+            size_t search_start,
             rm_detail_t *details,
             size_t nmatch,
             regmatch_t pmatch[],
***************
*** 219,224 ****
--- 221,227 ----
          v->pmatch = pmatch;
      v->details = details;
      v->start = (chr *) string;
+     v->search_start = (chr *) string + search_start;
      v->stop = (chr *) string + len;
      v->err = 0;
      if (backref)
***************
*** 288,294 ****
      NOERR();
      MDEBUG(("\nsearch at %ld\n", LOFF(v->start)));
      cold = NULL;
!     close = shortest(v, s, v->start, v->start, v->stop, &cold, (int *)
NULL);
      freedfa(s);
      NOERR();
      if (v->g->cflags & REG_EXPECT)
--- 291,298 ----
      NOERR();
      MDEBUG(("\nsearch at %ld\n", LOFF(v->start)));
      cold = NULL;
!     close = shortest(v, s, v->search_start, v->search_start, v->stop,
!                      &cold, (int *) NULL);
      freedfa(s);
      NOERR();
      if (v->g->cflags & REG_EXPECT)
***************
*** 415,421 ****

      assert(d != NULL && s != NULL);
      cold = NULL;
!     close = v->start;
      do
      {
          MDEBUG(("\ncsearch at %ld\n", LOFF(close)));
--- 419,425 ----

      assert(d != NULL && s != NULL);
      cold = NULL;
!     close = v->search_start;
      do
      {
          MDEBUG(("\ncsearch at %ld\n", LOFF(close)));
*** ./src/backend/utils/adt/regexp.c.orig    Tue Jul 20 08:50:08 2004
--- ./src/backend/utils/adt/regexp.c    Tue Jul 20 09:00:05 2004
***************
*** 80,116 ****


  /*
!  * RE_compile_and_execute - compile and execute a RE, caching if possible
   *
!  * Returns TRUE on match, FALSE on no match
   *
!  *    text_re --- the pattern, expressed as an *untoasted* TEXT object
!  *    dat --- the data to match against (need not be null-terminated)
!  *    dat_len --- the length of the data string
!  *    cflags --- compile options for the pattern
!  *    nmatch, pmatch    --- optional return area for match details
   *
!  * Both pattern and data are given in the database encoding.  We
internally
!  * convert to array of pg_wchar which is what Spencer's regex package
wants.
   */
! static bool
! RE_compile_and_execute(text *text_re, unsigned char *dat, int dat_len,
!                        int cflags, int nmatch, regmatch_t *pmatch)
  {
      int            text_re_len = VARSIZE(text_re);
-     pg_wchar   *data;
-     size_t        data_len;
      pg_wchar   *pattern;
      size_t        pattern_len;
      int            i;
      int            regcomp_result;
-     int            regexec_result;
      cached_re_str re_temp;

-     /* Convert data string to wide characters */
-     data = (pg_wchar *) palloc((dat_len + 1) * sizeof(pg_wchar));
-     data_len = pg_mb2wchar_with_len(dat, data, dat_len);
-
      /*
       * Look for a match among previously compiled REs.    Since the data
       * structure is self-organizing with most-used entries at the front,
--- 80,105 ----


  /*
!  * RE_compile_and_cache - compile a RE, caching if possible
   *
!  * Returns regex_t
   *
!  *  text_re --- the pattern, expressed as an *untoasted* TEXT object
!  *  cflags --- compile options for the pattern
   *
!  * Pattern is given in the database encoding.  We internally convert to
!  * array of pg_wchar which is what Spencer's regex package wants.
   */
! static regex_t
! RE_compile_and_cache(text *text_re, int cflags)
  {
      int            text_re_len = VARSIZE(text_re);
      pg_wchar   *pattern;
      size_t        pattern_len;
      int            i;
      int            regcomp_result;
      cached_re_str re_temp;

      /*
       * Look for a match among previously compiled REs.    Since the data
       * structure is self-organizing with most-used entries at the front,
***************
*** 132,149 ****
                  re_array[0] = re_temp;
              }

!             /* Perform RE match and return result */
!             regexec_result = pg_regexec(&re_array[0].cre_re,
!                                         data,
!                                         data_len,
!                                         NULL,    /* no details */
!                                         nmatch,
!                                         pmatch,
!                                         0);
!
!             pfree(data);
!
!             return (regexec_result == 0);
          }
      }

--- 121,127 ----
                  re_array[0] = re_temp;
              }

!             return re_array[0].cre_re;
          }
      }

***************
*** 210,219 ****
--- 188,231 ----
      re_array[0] = re_temp;
      num_res++;

+     return re_array[0].cre_re;
+ }
+
+ /*
+  * RE_compile_and_execute - compile and execute a RE, caching if possible
+  *
+  * Returns TRUE on match, FALSE on no match
+  *
+  *    text_re --- the pattern, expressed as an *untoasted* TEXT object
+  *    dat --- the data to match against (need not be null-terminated)
+  *    dat_len --- the length of the data string
+  *    cflags --- compile options for the pattern
+  *    nmatch, pmatch    --- optional return area for match details
+  *
+  * Both pattern and data are given in the database encoding.  We
internally
+  * convert to array of pg_wchar which is what Spencer's regex package
wants.
+  */
+ static bool
+ RE_compile_and_execute(text *text_re, unsigned char *dat, int dat_len,
+                        int cflags, int nmatch, regmatch_t *pmatch)
+ {
+     pg_wchar   *data;
+     size_t        data_len;
+     int            regexec_result;
+     regex_t        re;
+
+     /* Convert data string to wide characters */
+     data = (pg_wchar *) palloc((dat_len + 1) * sizeof(pg_wchar));
+     data_len = pg_mb2wchar_with_len(dat, data, dat_len);
+
+     /* Compile RE */
+     re = RE_compile_and_cache(text_re, cflags);
+
      /* Perform RE match and return result */
      regexec_result = pg_regexec(&re_array[0].cre_re,
                                  data,
                                  data_len,
+                                 0,
                                  NULL,    /* no details */
                                  nmatch,
                                  pmatch,
***************
*** 415,420 ****
--- 427,452 ----
      }

      PG_RETURN_NULL();
+ }
+
+ /*
+  * textregexreplace()
+  *      Return a replace string matched by a regular expression.
+  */
+ Datum
+ textregexreplace(PG_FUNCTION_ARGS)
+ {
+     text       *s = PG_GETARG_TEXT_P(0);
+     text       *p = PG_GETARG_TEXT_P(1);
+     text       *r = PG_GETARG_TEXT_P(2);
+     regex_t        re;
+
+     re = RE_compile_and_cache(p, regex_flavor);
+
+     return (DirectFunctionCall3(replace_text_regexp,
+                                 PointerGetDatum(s),
+                                 PointerGetDatum(&re),
+                                 PointerGetDatum(r)));
  }

  /* similar_escape()
*** ./src/backend/utils/adt/varlena.c.orig    Tue Jul 20 09:00:17 2004
--- ./src/backend/utils/adt/varlena.c    Tue Jul 20 10:23:32 2004
***************
*** 28,33 ****
--- 28,34 ----
  #include "utils/builtins.h"
  #include "utils/lsyscache.h"
  #include "utils/pg_locale.h"
+ #include "regex/regex.h"


  typedef struct varlena unknown;
***************
*** 1971,1976 ****
--- 1972,2122 ----
      ret_text = PG_STR_GET_TEXT(str->data);
      pfree(str->data);
      pfree(str);
+
+     PG_RETURN_TEXT_P(ret_text);
+ }
+
+ /*
+  * have_escape_in_regexp_replace_str
+  * check replace string have escape char
+  */
+ static bool
+ have_escape_in_regexp_replace_str(const char *replace_str)
+ {
+     return (strchr(replace_str, '\\') != NULL);
+ }
+
+ #define REGEXP_REPLACE_BACKREF_CNT        10
+ /*
+  * appendStringInfoRegexpSubstr
+  * append string for regexp back references.
+  */
+ static void
+ appendStringInfoRegexpSubstr(StringInfo str, text *replace_text,
+     regmatch_t *pmatch, text *buf_text, int search_start)
+ {
+     const char *pstart = PG_TEXT_GET_STR(replace_text);
+     const char *p = pstart;
+     const char *pnext;
+
+     text       *add_text;
+     int            so;
+     int            eo;
+
+     for(;;) {
+         pnext = strchr(p, '\\');
+         if(pnext == NULL) break;
+
+         add_text = text_substring(PointerGetDatum(replace_text),
+                                   p - pstart + 1, pnext - p, false);
+         appendStringInfoString(str, PG_TEXT_GET_STR(add_text));
+         pfree(add_text);
+
+         p = pnext + 1;
+         so = eo = -1;
+
+         if(*p >= '1' && *p <= '9') {
+             int        idx = *p - '0';
+             so = pmatch[idx].rm_so - search_start;
+             eo = pmatch[idx].rm_eo - search_start;
+             p++;
+         } else {
+             switch(*p) {
+             case '&':
+                 so = pmatch[0].rm_so - search_start;
+                 eo = pmatch[0].rm_eo - search_start;
+                 p++;
+                 break;
+             }
+         }
+
+         if(so != -1 && eo != -1) {
+             add_text = text_substring(PointerGetDatum(buf_text),
+                                       so + 1, (eo - so), false);
+             appendStringInfoString(str, PG_TEXT_GET_STR(add_text));
+             pfree(add_text);
+         }
+     }
+
+     add_text = text_substring(PointerGetDatum(replace_text),
+                               p - pstart + 1, -1, true);
+     appendStringInfoString(str, PG_TEXT_GET_STR(add_text));
+     pfree(add_text);
+ }
+
+ /*
+  * replace_text_regexp
+  * replace text using regexp
+ */
+ Datum
+ replace_text_regexp(PG_FUNCTION_ARGS)
+ {
+     text       *left_text;
+     text       *right_text;
+     text       *buf_text;
+     text       *ret_text;
+     text       *src_text = PG_GETARG_TEXT_P(0);
+     char       *src_text_str = PG_TEXT_GET_STR(src_text);
+     int            src_text_len = TEXTLEN(src_text);
+     regex_t       *re = (regex_t *)PG_GETARG_POINTER(1);
+     text       *replace_text = PG_GETARG_TEXT_P(2);
+     char       *replace_str = PG_TEXT_GET_STR(replace_text);
+     StringInfo    str = makeStringInfo();
+     int            regexec_result;
+     regmatch_t    pmatch[REGEXP_REPLACE_BACKREF_CNT];
+     pg_wchar   *data;
+     size_t        data_len;
+     int            search_start;
+     bool        have_escape;
+
+     buf_text = TEXTDUP(src_text);
+
+     /* Convert data string to wide characters */
+     data = (pg_wchar *) palloc((src_text_len + 1) * sizeof(pg_wchar));
+     data_len = pg_mb2wchar_with_len(src_text_str, data,
strlen(src_text_str));
+
+     have_escape = have_escape_in_regexp_replace_str(replace_str);
+
+     for(search_start = 0; search_start <= data_len;) {
+         regexec_result = pg_regexec(re,
+                                     data,
+                                     data_len,
+                                     search_start,
+                                     NULL,   /* no details */
+                                     REGEXP_REPLACE_BACKREF_CNT,
+                                     pmatch,
+                                     0);
+         if(regexec_result != 0) break;
+
+         left_text = text_substring(PointerGetDatum(buf_text),
+                                    1, pmatch[0].rm_so - search_start, false);
+         right_text = text_substring(PointerGetDatum(buf_text),
+                                    pmatch[0].rm_eo - search_start + 1,
+                                    -1, true);
+
+         appendStringInfoString(str, PG_TEXT_GET_STR(left_text));
+         if(have_escape) {
+             appendStringInfoRegexpSubstr(str, replace_text, pmatch,
+                                          buf_text, search_start);
+         } else {
+             appendStringInfoString(str, replace_str);
+         }
+
+         pfree(buf_text);
+         pfree(left_text);
+         buf_text = right_text;
+
+         search_start = pmatch[0].rm_eo;
+         if(pmatch[0].rm_so == pmatch[0].rm_eo) search_start++;
+     }
+
+     appendStringInfoString(str, PG_TEXT_GET_STR(buf_text));
+     pfree(buf_text);
+
+     ret_text = PG_STR_GET_TEXT(str->data);
+     pfree(str->data);
+     pfree(str);
+     pfree(data);

      PG_RETURN_TEXT_P(ret_text);
  }
*** ./src/include/catalog/pg_proc.h.orig    Tue Jul 20 09:24:11 2004
--- ./src/include/catalog/pg_proc.h    Tue Jul 20 09:26:11 2004
***************
*** 2186,2191 ****
--- 2186,2193 ----
  DESCR("return portion of string");
  DATA(insert OID =  2087 ( replace       PGNSP PGUID 12 f f t f i 3 25 "25 25
25"  replace_text - _null_ ));
  DESCR("replace all occurrences of old_substr with new_substr in string");
+ DATA(insert OID =  2167 ( regexp_replace    PGNSP PGUID 12 f f t f i 3 25
"25 25 25"  textregexreplace - _null_ ));
+ DESCR("replace text using regexp");
  DATA(insert OID =  2088 ( split_part   PGNSP PGUID 12 f f t f i 3 25 "25
25 23"  split_text - _null_ ));
  DESCR("split string by field_sep and return field_num");
  DATA(insert OID =  2089 ( to_hex       PGNSP PGUID 12 f f t f i 1 25 "23"
to_hex32 - _null_ ));
*** ./src/include/regex/regex.h.orig    Tue Jul 20 08:51:06 2004
--- ./src/include/regex/regex.h    Tue Jul 20 08:51:16 2004
***************
*** 163,169 ****
   * the prototypes for exported functions
   */
  extern int    pg_regcomp(regex_t *, const pg_wchar *, size_t, int);
! extern int    pg_regexec(regex_t *, const pg_wchar *, size_t, rm_detail_t *,
size_t, regmatch_t[], int);
  extern void pg_regfree(regex_t *);
  extern size_t pg_regerror(int, const regex_t *, char *, size_t);

--- 163,169 ----
   * the prototypes for exported functions
   */
  extern int    pg_regcomp(regex_t *, const pg_wchar *, size_t, int);
! extern int    pg_regexec(regex_t *, const pg_wchar *, size_t, size_t,
rm_detail_t *, size_t, regmatch_t[], int);
  extern void pg_regfree(regex_t *);
  extern size_t pg_regerror(int, const regex_t *, char *, size_t);

*** ./src/include/utils/builtins.h.orig    Tue Jul 20 09:11:19 2004
--- ./src/include/utils/builtins.h    Tue Jul 20 09:11:46 2004
***************
*** 408,413 ****
--- 408,414 ----
  extern Datum texticregexeq(PG_FUNCTION_ARGS);
  extern Datum texticregexne(PG_FUNCTION_ARGS);
  extern Datum textregexsubstr(PG_FUNCTION_ARGS);
+ extern Datum textregexreplace(PG_FUNCTION_ARGS);
  extern Datum similar_escape(PG_FUNCTION_ARGS);
  extern const char *assign_regex_flavor(const char *value,
                      bool doit, bool interactive);
***************
*** 537,542 ****
--- 538,544 ----
  extern bool SplitIdentifierString(char *rawstring, char separator,
                        List **namelist);
  extern Datum replace_text(PG_FUNCTION_ARGS);
+ extern Datum replace_text_regexp(PG_FUNCTION_ARGS);
  extern Datum split_text(PG_FUNCTION_ARGS);
  extern Datum text_to_array(PG_FUNCTION_ARGS);
  extern Datum array_to_text(PG_FUNCTION_ARGS);

__________________________________________________
Do You Yahoo!?
http://bb.yahoo.co.jp/