the patch include:

        - rename ichar() to chr() (discussed with Tom)

        - add docs for oracle compatible routines:

                btrim()
                ascii()
                chr()
                repeat()

        - fix bug with timezone in to_char()

        - all to_char() variants return NULL instead textin("")
          if it's needful.

 The contrib/odbc is without changes and contains same routines as main
tree ... because I not sure how plans are Thomas with this :-)

                                        Karel
---------------------------------------------------------------------------

This effectively one line patch should fix the fact that
foreign key definitions in create table were erroring if
a primary key was defined.  I was using the columns
list to get the columns of the table for comparison, but
it got reused as a temporary list inside the primary key
stuff.

Stephan Szabo

5 files changed, 102 insertions, 28 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0306f7cf05c..f7b2334f3dc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -356,10 +356,16 @@
       </thead>
       <tbody>
        <row>
-	<entry>to_ascii(text [,name|int])</entry>
+	<entry>ascii(text)</entry>
+	<entry>int</entry>
+	<entry>returns the decimal representation of the first character from text</entry>
+	<entry>ascii('x')</entry>
+       </row>
+       <row>
+	<entry>btrim(text,set)</entry>
 	<entry>text</entry>
-	<entry>convert text from multibyte encoding to ASCII</entry>
-	<entry>to_ascii('Karel')</entry>
+	<entry>both (left and right) trim characters from text</entry>
+	<entry>btrim('xxxtrimxxx','x')</entry>
        </row>
        <row>
 	<entry>char(text)</entry>
@@ -374,6 +380,12 @@
 	<entry>char(varchar 'varchar string')</entry>
        </row>
        <row>
+       <row>
+	<entry>chr(int)</entry>
+	<entry>text</entry>
+	<entry>returns the character having the binary equivalent to int</entry>
+	<entry>chr(65)</entry>
+       </row>
 	<entry>initcap(text)</entry>
 	<entry>text</entry>
 	<entry>first letter of each word to upper case</entry>
@@ -392,10 +404,10 @@
 	<entry>ltrim('xxxxtrim','x')</entry>
        </row>
        <row>
-	<entry>textpos(text,text)</entry>
+	<entry>repeat(text,int)</entry>
 	<entry>text</entry>
-	<entry>locate specified substring</entry>
-	<entry>position('high','ig')</entry>
+	<entry>repeat text by int</entry>
+	<entry>repeat('Pg', 4)</entry>
        </row>
        <row>
 	<entry>rpad(text,int,text)</entry>
@@ -428,11 +440,23 @@
 	<entry>text(varchar 'varchar string')</entry>
        </row>
        <row>
+	<entry>textpos(text,text)</entry>
+	<entry>text</entry>
+	<entry>locate specified substring</entry>
+	<entry>position('high','ig')</entry>
+       </row>
+       <row>
+	<entry>to_ascii(text [,name|int])</entry>
+	<entry>text</entry>
+	<entry>convert text from multibyte encoding to ASCII</entry>
+	<entry>to_ascii('Karel')</entry>
+       </row>
+       <row>
 	<entry>translate(text,from,to)</entry>
 	<entry>text</entry>
 	<entry>convert character in string</entry>
 	<entry>translate('12345', '1', 'a')</entry>
-       </row>
+       </row>       
        <row>
 	<entry>varchar(char)</entry>
 	<entry>varchar</entry>
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0165ef15c21..888f8f8e144 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$Id: analyze.c,v 1.157 2000/09/12 21:07:00 tgl Exp $
+ *	$Id: analyze.c,v 1.158 2000/09/25 12:58:46 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1083,7 +1083,7 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt)
 				foreach(fkattrs, fkconstraint->fk_attrs) {
 					found=0;
 					fkattr=lfirst(fkattrs);
-					foreach(cols, columns) {
+					foreach(cols, stmt->tableElts) {
 						col=lfirst(cols);
 						if (strcmp(col->colname, fkattr->name)==0) {
 							found=1;
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8dcce7f1d90..748bb694d79 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -1,7 +1,7 @@
 /* -----------------------------------------------------------------------
  * formatting.c
  *
- * $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.21 2000/08/29 04:41:47 momjian Exp $
+ * $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.22 2000/09/25 12:58:47 momjian Exp $
  *
  *
  *	 Portions Copyright (c) 1999-2000, PostgreSQL, Inc
@@ -1742,7 +1742,7 @@ dch_time(int arg, char *inout, int suf, int flag, FormatNode *node)
 			break;
 		case DCH_tz:
 		case DCH_TZ:
-			if (flag == TO_CHAR)
+			if (flag == TO_CHAR && tzn)
 			{
 				int siz = strlen(tzn);
 				
@@ -2452,7 +2452,7 @@ timestamp_to_char(PG_FUNCTION_ARGS)
 	len = VARSIZE(fmt) - VARHDRSZ;
 
 	if (len <= 0 || TIMESTAMP_NOT_FINITE(dt))
-		return DirectFunctionCall1(textin, CStringGetDatum(""));
+		PG_RETURN_NULL();
 
 	ZERO_tm(tm);	
 	tzn = NULL;
@@ -2552,7 +2552,12 @@ timestamp_to_char(PG_FUNCTION_ARGS)
 	 * needs, now it must be re-allocate to result real size
 	 * ----------
 	 */
-	len = strlen(VARDATA(result));
+	if (!(len = strlen(VARDATA(result))))
+	{
+		pfree(result);
+		PG_RETURN_NULL();
+	}
+	
 	result_tmp = result;
 	result = (text *) palloc(len + 1 + VARHDRSZ);
 
@@ -4017,12 +4022,17 @@ do { \
 	if (flag)							\
 		pfree(format);						\
 									\
-	/* ----------						\
+	/* ----------							\
 	 * for result is allocated max memory, which current format-picture\
 	 * needs, now it must be re-allocate to result real size	\
 	 * ----------							\
 	 */								\
-	len		= strlen(VARDATA(result));			\
+	if (!(len = strlen(VARDATA(result))))				\
+	{								\
+		pfree(result);						\
+		PG_RETURN_NULL();					\
+	}								\
+									\
 	result_tmp	= result;					\
 	result		= (text *) palloc( len + 1 + VARHDRSZ);		\
 									\
diff --git a/src/backend/utils/adt/oracle_compat.c b/src/backend/utils/adt/oracle_compat.c
index db49f7d9e8b..8b483ee5c60 100644
--- a/src/backend/utils/adt/oracle_compat.c
+++ b/src/backend/utils/adt/oracle_compat.c
@@ -1,7 +1,7 @@
 /*
  *	Edmund Mergl <E.Mergl@bawue.de>
  *
- *	$Header: /cvsroot/pgsql/src/backend/utils/adt/oracle_compat.c,v 1.27 2000/07/06 05:48:11 tgl Exp $
+ *	$Header: /cvsroot/pgsql/src/backend/utils/adt/oracle_compat.c,v 1.28 2000/09/25 12:58:47 momjian Exp $
  *
  */
 
@@ -515,6 +515,20 @@ translate(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(result);
 }
 
+/********************************************************************
+ *
+ * ascii
+ *
+ * Syntax:
+ *
+ *	 int ascii(text string)
+ *
+ * Purpose:
+ *
+ *	 Returns the decimal representation of the first character from
+ *	 string.
+ *
+ ********************************************************************/
 
 Datum
 ascii(PG_FUNCTION_ARGS)
@@ -527,12 +541,25 @@ ascii(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32((int32) *((unsigned char *) VARDATA(string)));
 }
 
+/********************************************************************
+ *
+ * chr
+ *
+ * Syntax:
+ *
+ *	 text chr(int val)
+ *
+ * Purpose:
+ *
+ *	Returns the character having the binary equivalent to val
+ *
+ ********************************************************************/
 
 Datum
-ichar(PG_FUNCTION_ARGS)
+chr(PG_FUNCTION_ARGS)
 {
-	int32		cvalue = PG_GETARG_INT32(0);
-	text	   *result;
+	int32	cvalue = PG_GETARG_INT32(0);
+	text	*result;
 
 	result = (text *) palloc(VARHDRSZ + 1);
 	VARATT_SIZEP(result) = VARHDRSZ + 1;
@@ -541,17 +568,30 @@ ichar(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(result);
 }
 
+/********************************************************************
+ *
+ * repeat
+ *
+ * Syntax:
+ *
+ *	 text repeat(text string, int val)
+ *
+ * Purpose:
+ *
+ *	Repeat string by val. 
+ *
+ ********************************************************************/
 
 Datum
 repeat(PG_FUNCTION_ARGS)
 {
-	text	   *string = PG_GETARG_TEXT_P(0);
-	int32		count = PG_GETARG_INT32(1);
-	text	   *result;
-	int			slen,
-				tlen;
-	int			i;
-	char	   *cp;
+	text	*string = PG_GETARG_TEXT_P(0);
+	int32	count = PG_GETARG_INT32(1);
+	text	*result;
+	int	slen,
+		tlen;
+	int	i;
+	char	*cp;
 
 	if (count < 0)
 		count = 0;
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 4f2043fb02a..920aaa442d0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_proc.h,v 1.167 2000/09/19 18:18:01 petere Exp $
+ * $Id: pg_proc.h,v 1.168 2000/09/25 12:58:47 momjian Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -2036,7 +2036,7 @@ DESCR("convert int4 to varchar");
 
 DATA(insert OID = 1620 (  ascii				PGUID 12 f t t t 1 f 23 "25" 100 0 0 100	ascii - ));
 DESCR("convert first char to int4");
-DATA(insert OID = 1621 (  ichar				PGUID 12 f t t t 1 f 25 "23" 100 0 0 100	ichar - ));
+DATA(insert OID = 1621 (  chr				PGUID 12 f t t t 1 f 25 "23" 100 0 0 100	chr - ));
 DESCR("convert int4 to char");
 DATA(insert OID = 1622 (  repeat			PGUID 12 f t t t 2 f 25 "25 23" 100 0 0 100  repeat - ));
 DESCR("replicate string int4 times");