PostgreSQL 正则表达式 常用函数的总结

[root@localhost regex]# pwd

/opt/hgdb-core/src/include/regex

[root@localhost regex]# ll

total 40

-rw-r--r--. 1 postgres postgres 3490 Mar 19 19:00 regcustom.h

-rw-r--r--. 1 postgres postgres 1332 Mar 19 18:59 regerrs.h

-rw-r--r--. 1 postgres postgres 6703 Mar 19 19:00 regex.h

-rw-r--r--. 1 postgres postgres 2353 Mar 19 19:00 regexport.h

-rw-r--r--. 1 postgres postgres 16454 Mar 19 19:00 regguts.h

[root@localhost regex]# pwd

/opt/hgdb-core/src/backend/regex

[root@localhost regex]# ll reg*.c

-rw-r --r--. 1 postgres postgres 55851 Mar 19 19:00 regcomp.c

-rw-r --r--. 1 postgres postgres 3671 Mar 19 18:59 regerror.c

-rw-r --r--. 1 postgres postgres 34873 Mar 19 19:00 regexec.c

-rw-r --r--. 1 postgres postgres 2123 Mar 19 18:59 regfree.c

[root@localhost regex]#

[root@localhost adt]# pwd

/opt/hgdb-core/src/backend/utils/adt

[root@localhost adt]# ll regexp.c

-rw-r --r--. 1 postgres postgres 34863 Apr 12 02:29 regexp.c

[root@localhost adt]#

/* src/include/catalog/pg_proc.h */

DATA( insert OID = 2073 ( substring   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 25 "25 25" _null_ _null_ _null_ _null_ _null_ textregexsubstr _null_ _null_ _null_ ));

DESCR( "extract text matching regular expression" );

DATA( insert OID = 2074 ( substring   PGNSP PGUID 14 1 0 0 0 f f f f t f i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_ _null_ "select pg_catalog.substring($1, pg_catalog.similar_escape($2, $3))" _null_ _null_ _null_ ));

DESCR( "extract text matching SQL99 regular expression" );

DATA( insert OID = 2284 ( regexp_replace  PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_ _null_ textregexreplace_noopt _null_ _null_ _null_ ));

DESCR( "replace text using regexp" );

DATA( insert OID = 2285 ( regexp_replace  PGNSP PGUID 12 1 0 0 0 f f f f t f i 4 0 25 "25 25 25 25" _null_ _null_ _null_ _null_ _null_ textregexreplace _null_ _null_ _null_ ));

DESCR( "replace text using regexp" );

DATA( insert OID = 2763 ( regexp_matches  PGNSP PGUID 12 1 1 0 0 f f f f t t i 2 0 1009 "25 25" _null_ _null_ _null_ _null_ _null_ regexp_matches_no_flags _null_ _null_ _null_ ));

DESCR( "find all match groups for regexp" );

DATA( insert OID = 2764 ( regexp_matches  PGNSP PGUID 12 1 10 0 0 f f f f t t i 3 0 1009 "25 25 25" _null_ _null_ _null_ _null_ _null_ regexp_matches _null_ _null_ _null_ ));

DESCR( "find all match groups for regexp" );

DATA( insert OID = 2765 ( regexp_split_to_table PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 25 "25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_table_no_flags _null_ _null_ _null_ ));

DESCR( "split string by pattern" );

DATA( insert OID = 2766 ( regexp_split_to_table PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_table _null_ _null_ _null_ ));

DESCR( "split string by pattern" );

DATA( insert OID = 2767 ( regexp_split_to_array PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1009 "25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_array_no_flags _null_ _null_ _null_ ));

DESCR( "split string by pattern" );

DATA( insert OID = 2768 ( regexp_split_to_array PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1009 "25 25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_array _null_ _null_ _null_ ));

postgres=# select oid,typname from pg_type where oid = 25 or oid = 1009;

  oid | typname

------+---------

   25 | text

  1009 | _text

(2 rows )

/* the biggie, a compiled RE ( or rather, a front end to same) */

typedef struct

{

  int   re_magic; /* magic number */

  size_t re_nsub; /* number of subexpressions */

  long re_info; /* information about RE */

#define REG_UBACKREF  000001

#define REG_ULOOKAHEAD  000002

#define REG_UBOUNDS 000004

#define REG_UBRACES 000010

#define REG_UBSALNUM  000020

#define REG_UPBOTCH 000040

#define REG_UBBS  000100

#define REG_UNONPOSIX  000200

#define REG_UUNSPEC 000400

#define REG_UUNPORT 001000

#define REG_ULOCALE 002000

#define REG_UEMPTYMATCH 004000

#define REG_UIMPOSSIBLE 010000

#define REG_USHORTEST  020000

  int   re_csize; /* sizeof( character ) */

  char   *re_endp; /* backward compatibility kludge */

  Oid  re_collation; /* Collation that defines LC_CTYPE behavior */

  /* the rest is opaque pointers to hidden innards */

  char   *re_guts; /* ` char * ' is more portable than `void *' */

  char   *re_fns;

} regex_t;

/* result reporting (may acquire more fields later) */

typedef struct

{

  regoff_t rm_so;  /* start of substring */

  regoff_t rm_eo;  /* end of substring */

} regmatch_t;

typedef long regoff_t;

/*

  * the prototypes for exported functions

  */

extern int pg_regcomp(regex_t *, const pg_wchar *, size_t, int , Oid);

extern int pg_regexec(regex_t *, const pg_wchar *, size_t, size_t, rm_detail_t *, size_t, regmatch_t[], int );

extern int pg_regprefix(regex_t *, pg_wchar **, size_t *);

extern void pg_regfree(regex_t *);

extern size_t pg_regerror( int , const regex_t *, char *, size_t);

extern void pg_set_regex_collation(Oid collation);

int

pg_regcomp(regex_t *re,

    const chr *string, /* 正则表达式字符串 */

    size_t len, /* 正则表达式字符串长度 */

    int flags,

    Oid collation)

int

pg_regexec(regex_t *re, /* 已经用regcomp函数编译好的正则表达式 */

    const chr *string, /* 目标字符串 */

    size_t len, /* 目标字符串长度 */

    size_t search_start, /* 匹配开始位置 */

    rm_detail_t *details, /* NULL */

    size_t nmatch, /* 是regmatch_t结构体数组的长度 */

    regmatch_t pmatch[], /* regmatch_t类型的结构体数组，存放匹配文本串的位置信息 */

    int flags)

/* all the options of interest for regex functions */

typedef struct pg_re_flags

{

  int   cflags;  /* compile flags for Spencer's regex code */

  bool glob;  /* do it globally ( for each occurrence) */

} pg_re_flags;

/*

  * parse_re_flags - parse the options argument of regexp_matches and friends

  *

  * flags --- output argument, filled with desired options

  * opts --- TEXT object, or NULL for defaults

  *

  * This accepts all the options allowed by any of the callers; callers that

  * don 't want some have to reject them after the fact.

  */

static void

parse_re_flags(pg_re_flags *flags, text *opts)

{

  /* regex flavor is always folded into the compile flags */

  flags->cflags = REG_ADVANCED;

  flags->glob = false;

  if (opts)

  {

  char  *opt_p = VARDATA_ANY(opts);

  int  opt_len = VARSIZE_ANY_EXHDR(opts);

  int  i;

  for (i = 0; i < opt_len; i++)

  {

   switch (opt_p[i])

   {

   case ' g ':

    flags->glob = true;

    break;

   case ' b ': /* BREs (but why???) */

    flags->cflags &= ~(REG_ADVANCED | REG_EXTENDED | REG_QUOTE);

    break;

   case ' c ': /* case sensitive */

    flags->cflags &= ~REG_ICASE;

    break;

   case ' e ': /* plain EREs */

    flags->cflags |= REG_EXTENDED;

    flags->cflags &= ~(REG_ADVANCED | REG_QUOTE);

    break;

   case ' i ': /* case insensitive */

    flags->cflags |= REG_ICASE;

    break;

   case ' m ': /* Perloid synonym for n */

   case ' n ': /* affects ^ $ . [^ */

    flags->cflags |= REG_NEWLINE;

    break;

   case ' p ': /* ~Perl, affects . [^ */

    flags->cflags |= REG_NLSTOP;

    flags->cflags &= ~REG_NLANCH;

    break;

   case ' q ': /* literal string */

    flags->cflags |= REG_QUOTE;

    flags->cflags &= ~(REG_ADVANCED | REG_EXTENDED);

    break;

   case ' s ': /* single line, ordinary */

    flags->cflags &= ~REG_NEWLINE;

    break;

   case ' t ': /* tight syntax */

    flags->cflags &= ~REG_EXPANDED;

    break;

   case ' w ': /* weird, affects ^ $ only */

    flags->cflags &= ~REG_NLSTOP;

    flags->cflags |= REG_NLANCH;

    break;

   case ' x': /* expanded syntax */

    flags->cflags |= REG_EXPANDED;

    break;

   default :

    ereport(ERROR,

     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),

     errmsg( "invalid regexp option: "%c"" ,

      opt_p[i])));

    break;

   }

  }

  }

}