Implement SQL99 OVERLAY(). Allows substitution of a substring in a string.

Implement SQL99 SIMILAR TO as a synonym for our existing operator "~".
Implement SQL99 regular expression SUBSTRING(string FROM pat FOR escape).
 Extend the definition to make the FOR clause optional.
 Define textregexsubstr() to actually implement this feature.
Update the regression test to include these new string features.
 All tests pass.
Rename the regular expression support routines from "pg95_xxx" to "pg_xxx".
Define CREATE CHARACTER SET in the parser per SQL99. No implementation yet.

4 files changed, 24 insertions, 13 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index bd861396210..ae10bfb6879 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: catversion.h,v 1.133 2002/05/22 17:21:01 petere Exp $
+ * $Id: catversion.h,v 1.134 2002/06/11 15:44:38 thomas Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200205221
+#define CATALOG_VERSION_NO	200206111
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a7d56dc90dc..56831dcfca1 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_proc.h,v 1.240 2002/05/24 18:57:56 tgl Exp $
+ * $Id: pg_proc.h,v 1.241 2002/06/11 15:41:37 thomas Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -1679,7 +1679,7 @@ DESCR("less-equal-greater");
 DATA(insert OID = 1359 (  timestamptz	   PGNSP PGUID 12 f f f t f i 2 1184 "1082 1266" 100 0 0 100  datetimetz_timestamptz - _null_ ));
 DESCR("convert date and time with time zone to timestamp with time zone");
 
-DATA(insert OID = 1364 (  time				 PGNSP PGUID 14 f f f t f i 1 1083 "702" 100 0 0 100  "select time(cast($1 as timestamp without time zone))" - _null_ ));
+DATA(insert OID = 1364 (  time			   PGNSP PGUID 14 f f f t f i 1 1083 "702" 100 0 0 100  "select time(cast($1 as timestamp without time zone))" - _null_ ));
 DESCR("convert abstime to time");
 
 DATA(insert OID = 1367 (  character_length	PGNSP PGUID 12 f f f t f i 1	23 "1042" 100 0 0 100  bpcharlen - _null_ ));
@@ -1764,6 +1764,11 @@ DESCR("current schema name");
 DATA(insert OID = 1403 (  current_schemas	PGNSP PGUID 12 f f f t f s 0  1003 "0" 100 0 0 100	current_schemas - _null_ ));
 DESCR("current schema search list");
 
+DATA(insert OID = 1404 (  overlay			PGNSP PGUID 14 f f f t f i 4 25 "25 25 23 23" 100 0 0 100  "select substring($1, 1, ($3 - 1)) || $2 || substring($1, ($3 + $4))" - _null_ ));
+DESCR("substitute portion of string");
+DATA(insert OID = 1405 (  overlay			PGNSP PGUID 14 f f f t f i 3 25 "25 25 23" 100 0 0 100  "select substring($1, 1, ($3 - 1)) || $2 || substring($1, ($3 + char_length($2)))" - _null_ ));
+DESCR("substitute portion of string");
+
 DATA(insert OID = 1406 (  isvertical		PGNSP PGUID 12 f f f t f i 2	16 "600 600" 100 0 0 100  point_vert - _null_ ));
 DESCR("vertically aligned?");
 DATA(insert OID = 1407 (  ishorizontal		PGNSP PGUID 12 f f f t f i 2	16 "600 600" 100 0 0 100  point_horiz - _null_ ));
@@ -2871,6 +2876,11 @@ DESCR("add");
 DATA(insert OID = 2072 (  date_mi_interval	PGNSP PGUID 14 f f f t f i 2 1114 "1082 1186" 100 0 0 100  "select cast($1 as timestamp without time zone) - $2;" - _null_ ));
 DESCR("subtract");
 
+DATA(insert OID = 2073 (  substring			PGNSP PGUID 12 f f f t f i 2 25 "25 25" 100 0 0 100	textregexsubstr - _null_ ));
+DESCR("substitutes regular expression");
+DATA(insert OID = 2074 (  substring			PGNSP PGUID 14 f f f t f i 3 25 "25 25 25" 100 0 0 100	"select substring($1, like_escape($2, $3))" - _null_ ));
+DESCR("substitutes regular expression with escape argument");
+
 /* Aggregates (moved here from pg_aggregate for 7.3) */
 
 DATA(insert OID = 2100 (  avg				PGNSP PGUID 12 t f f f f i 1 1700 "20" 100 0 0 100  aggregate_dummy - _null_ ));
diff --git a/src/include/regex/regex.h b/src/include/regex/regex.h
index 09f966e1bf2..dd8e0da171b 100644
--- a/src/include/regex/regex.h
+++ b/src/include/regex/regex.h
@@ -53,7 +53,7 @@ typedef struct
 	const pg_wchar *re_endp;	/* end pointer for REG_PEND */
 	struct re_guts *re_g;		/* none of your business :-) */
 #ifdef MULTIBYTE
-	pg_wchar   *patsave;		/* mee too :-) */
+	pg_wchar   *patsave;		/* me too :-) */
 #endif
 
 } regex_t;
@@ -102,12 +102,12 @@ typedef struct
 #define REG_LARGE		01000	/* force large representation */
 #define REG_BACKR		02000	/* force use of backref code */
 
-extern int	pg95_regcomp(regex_t *preg, const char *pattern, int cflags);
-extern size_t pg95_regerror(int errcode, const regex_t *preg,
-			  char *errbuf, size_t errbuf_size);
-extern int pg95_regexec(const regex_t *preg, const char *string,
-			 size_t nmatch,
-			 regmatch_t *pmatch, int eflags);
-extern void pg95_regfree(regex_t *preg);
+extern int	pg_regcomp(regex_t *preg, const char *pattern, int cflags);
+extern size_t pg_regerror(int errcode, const regex_t *preg,
+						  char *errbuf, size_t errbuf_size);
+extern int pg_regexec(const regex_t *preg, const char *string,
+					  size_t nmatch,
+					  regmatch_t *pmatch, int eflags);
+extern void pg_regfree(regex_t *preg);
 
 #endif   /* !_REGEX_H_ */
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 6340aa22540..7676ce5663f 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: builtins.h,v 1.182 2002/05/18 21:38:41 tgl Exp $
+ * $Id: builtins.h,v 1.183 2002/06/11 15:41:38 thomas Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -327,6 +327,7 @@ extern Datum nameicregexeq(PG_FUNCTION_ARGS);
 extern Datum nameicregexne(PG_FUNCTION_ARGS);
 extern Datum texticregexeq(PG_FUNCTION_ARGS);
 extern Datum texticregexne(PG_FUNCTION_ARGS);
+extern Datum textregexsubstr(PG_FUNCTION_ARGS);
 
 /* regproc.c */
 extern Datum regprocin(PG_FUNCTION_ARGS);