I noticed that SQL:2003 specifies explicit casts between "boolean" and
the character string types. Attached is a patch that implements them,
and adds some simple regression tests.

A few points worth noting:

(1) The SQL spec requires that text::boolean trim leading and trailing
whitespace from the input

(2) The spec also requires that boolean::varchar(n) should raise an
error if "n" is not large enough to accomodate the textual
representation of the boolean value. We currently truncate:

=> select true::boolean::varchar(3);
 varchar 
---------
 TRU

Not sure offhand if there's an easy way to satisfy the spec's
requirement...

(3) The spec suggests that true/false should be upper-cased when
converted to text, so that's what I've implemented, but one could argue
that converting to lower-case would be more consistent with PG's general
approach to case folding.

-Neil

Index: src/backend/utils/adt/bool.c
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/utils/adt/bool.c,v
retrieving revision 1.38
diff -p -c -r1.38 bool.c
*** src/backend/utils/adt/bool.c	5 Jan 2007 22:19:40 -0000	1.38
--- src/backend/utils/adt/bool.c	28 May 2007 18:47:59 -0000
*************** boolsend(PG_FUNCTION_ARGS)
*** 127,132 ****
--- 127,177 ----
  	PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
  }
  
+ /*
+  *	    textbool			- cast function for text => bool
+  */
+ Datum
+ textbool(PG_FUNCTION_ARGS)
+ {
+ 	text 		*arg1 = PG_GETARG_TEXT_P(0);
+ 	text 		*trim;
+ 	char 		*trim_str;
+ 
+ 	trim = DatumGetTextP(DirectFunctionCall1(btrim1,
+ 											 PointerGetDatum(arg1)));
+ 	trim_str = DatumGetCString(DirectFunctionCall1(textout,
+ 												   PointerGetDatum(trim)));
+ 
+ 	if (pg_strcasecmp(trim_str, "true") == 0)
+ 		PG_RETURN_BOOL(true);
+ 	else if (pg_strcasecmp(trim_str, "false") == 0)
+ 		PG_RETURN_BOOL(false);
+ 	else
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_INVALID_CHARACTER_VALUE_FOR_CAST),
+ 				 errmsg("invalid input syntax for type boolean: \"%s\"",
+ 						trim_str)));
+ 
+ 	PG_RETURN_BOOL(false);		/* keep the compiler quiet */
+ }
+ 
+ /*
+  *	    booltext			- cast function for bool => text
+  */
+ Datum
+ booltext(PG_FUNCTION_ARGS)
+ {
+ 	bool 		 arg1 = PG_GETARG_BOOL(0);
+ 	char 		*str;
+ 
+ 	if (arg1)
+ 		str = "TRUE";
+ 	else
+ 		str = "FALSE";
+ 
+ 	PG_RETURN_DATUM(DirectFunctionCall1(textin, CStringGetDatum(str)));
+ }
+ 
  
  /*****************************************************************************
   *	 PUBLIC ROUTINES														 *
Index: src/include/catalog/pg_cast.h
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_cast.h,v
retrieving revision 1.32
diff -p -c -r1.32 pg_cast.h
*** src/include/catalog/pg_cast.h	2 Apr 2007 03:49:40 -0000	1.32
--- src/include/catalog/pg_cast.h	28 May 2007 18:41:38 -0000
*************** DATA(insert ( 1700	 25 1688 i ));
*** 302,307 ****
--- 302,309 ----
  DATA(insert (	25 1700 1686 e ));
  DATA(insert (  142   25 2922 e ));
  DATA(insert (   25  142	2896 e ));
+ DATA(insert (   16   25 2971 e ));
+ DATA(insert (   25   16 2970 e ));
  
  /*
   * Cross-category casts to and from VARCHAR
*************** DATA(insert ( 1700 1043 1688 a ));
*** 342,347 ****
--- 344,351 ----
  DATA(insert ( 1043 1700 1686 e ));
  DATA(insert (  142 1043 2922 e ));
  DATA(insert ( 1043  142 2896 e ));
+ DATA(insert (   16 1043 2971 e ));
+ DATA(insert ( 1043   16 2970 e ));
  
  /*
   * Cross-category casts to and from BPCHAR
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.456
diff -p -c -r1.456 pg_proc.h
*** src/include/catalog/pg_proc.h	21 May 2007 17:10:29 -0000	1.456
--- src/include/catalog/pg_proc.h	28 May 2007 18:40:53 -0000
*************** DESCR("List all files in a directory");
*** 3221,3226 ****
--- 3221,3230 ----
  DATA(insert OID = 2626 ( pg_sleep			PGNSP PGUID 12 1 0 f f t f v 1 2278 "701" _null_ _null_ _null_ pg_sleep - _null_ ));
  DESCR("Sleep for the specified time in seconds");
  
+ DATA(insert OID = 2970 (  boolean			PGNSP PGUID 12 1 0 f f t f i 1 16 "25" _null_ _null_ _null_	textbool - _null_ ));
+ DESCR("text to boolean");
+ DATA(insert OID = 2971 (  text				PGNSP PGUID 12 1 0 f f t f i 1 25 "16" _null_ _null_ _null_	booltext - _null_ ));
+ DESCR("boolean to text");
  
  /* Aggregates (moved here from pg_aggregate for 7.3) */
  
Index: src/include/utils/builtins.h
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.293
diff -p -c -r1.293 builtins.h
*** src/include/utils/builtins.h	17 May 2007 23:31:49 -0000	1.293
--- src/include/utils/builtins.h	28 May 2007 18:13:15 -0000
*************** extern Datum boolin(PG_FUNCTION_ARGS);
*** 70,75 ****
--- 70,77 ----
  extern Datum boolout(PG_FUNCTION_ARGS);
  extern Datum boolrecv(PG_FUNCTION_ARGS);
  extern Datum boolsend(PG_FUNCTION_ARGS);
+ extern Datum booltext(PG_FUNCTION_ARGS);
+ extern Datum textbool(PG_FUNCTION_ARGS);
  extern Datum booleq(PG_FUNCTION_ARGS);
  extern Datum boolne(PG_FUNCTION_ARGS);
  extern Datum boollt(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/boolean.out
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/boolean.out,v
retrieving revision 1.12
diff -p -c -r1.12 boolean.out
*** src/test/regress/expected/boolean.out	7 Apr 2005 01:51:40 -0000	1.12
--- src/test/regress/expected/boolean.out	28 May 2007 19:11:38 -0000
*************** SELECT bool 't' <> bool 'f' AS true;
*** 54,59 ****
--- 54,81 ----
   t
  (1 row)
  
+ -- explicit casts to/from text
+ SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false;
+  true | false 
+ ------+-------
+  t    | f
+ (1 row)
+ 
+ SELECT '    true   '::text::boolean AS true,
+        '     FALSE'::text::boolean AS false;
+  true | false 
+ ------+-------
+  t    | f
+ (1 row)
+ 
+ SELECT true::boolean::text AS true, false::boolean::text AS false;
+  true | false 
+ ------+-------
+  TRUE | FALSE
+ (1 row)
+ 
+ SELECT '  tru e '::text::boolean AS invalid; -- error
+ ERROR:  invalid input syntax for type boolean: "tru e"
  CREATE TABLE BOOLTBL1 (f1 bool);
  INSERT INTO BOOLTBL1 (f1) VALUES (bool 't');
  INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True');
Index: src/test/regress/sql/boolean.sql
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/sql/boolean.sql,v
retrieving revision 1.7
diff -p -c -r1.7 boolean.sql
*** src/test/regress/sql/boolean.sql	7 Apr 2005 01:51:41 -0000	1.7
--- src/test/regress/sql/boolean.sql	28 May 2007 18:59:33 -0000
*************** SELECT bool 't' = bool 'f' AS false;
*** 26,31 ****
--- 26,39 ----
  
  SELECT bool 't' <> bool 'f' AS true;
  
+ -- explicit casts to/from text
+ SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false;
+ SELECT '    true   '::text::boolean AS true,
+        '     FALSE'::text::boolean AS false;
+ SELECT true::boolean::text AS true, false::boolean::text AS false;
+ 
+ SELECT '  tru e '::text::boolean AS invalid; -- error
+ 
  
  CREATE TABLE BOOLTBL1 (f1 bool);
  
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Reply via email to