On Mon, 2007-28-05 at 15:38 -0400, Tom Lane wrote:
> More generally, I'm really hoping to get rid of bespoke text<->whatever
> cast functions in favor of using datatypes' I/O functions.  To what
> extent can we make the boolean I/O functions serve for this?  It seems
> relatively painless on the input side --- just allow whitespace --- but
> I suppose we can't change boolout's historical result of "t"/"f" without
> causing problems.

Attached is a revised version of this patch that modifies boolin() to
ignore leading and trailing whitespace. This makes text => boolean
trivial, but boolean => text is still distinct from boolout().

Barring any objections, I'll apply this later today or tomorrow.

-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	30 May 2007 18:55:26 -0000
***************
*** 15,20 ****
--- 15,22 ----
  
  #include "postgres.h"
  
+ #include <ctype.h>
+ 
  #include "libpq/pqformat.h"
  #include "utils/builtins.h"
  
***************
*** 33,73 ****
  Datum
  boolin(PG_FUNCTION_ARGS)
  {
! 	char	   *b = PG_GETARG_CSTRING(0);
  
! 	switch (*b)
  	{
  		case 't':
  		case 'T':
! 			if (pg_strncasecmp(b, "true", strlen(b)) == 0)
  				PG_RETURN_BOOL(true);
  			break;
  
  		case 'f':
  		case 'F':
! 			if (pg_strncasecmp(b, "false", strlen(b)) == 0)
  				PG_RETURN_BOOL(false);
  			break;
  
  		case 'y':
  		case 'Y':
! 			if (pg_strncasecmp(b, "yes", strlen(b)) == 0)
  				PG_RETURN_BOOL(true);
  			break;
  
  		case '1':
! 			if (pg_strncasecmp(b, "1", strlen(b)) == 0)
  				PG_RETURN_BOOL(true);
  			break;
  
  		case 'n':
  		case 'N':
! 			if (pg_strncasecmp(b, "no", strlen(b)) == 0)
  				PG_RETURN_BOOL(false);
  			break;
  
  		case '0':
! 			if (pg_strncasecmp(b, "0", strlen(b)) == 0)
  				PG_RETURN_BOOL(false);
  			break;
  
--- 35,88 ----
  Datum
  boolin(PG_FUNCTION_ARGS)
  {
! 	const char	*in_str = PG_GETARG_CSTRING(0);
! 	const char 	*str;
! 	size_t 		 len;
! 
! 	/*
! 	 * Skip leading and trailing whitespace
! 	 */
! 	str = in_str;
! 	while (isspace((unsigned char) *str))
! 		str++;
! 
! 	len = strlen(str);
! 	while (len > 0 && isspace((unsigned char) str[len - 1]))
! 		len--;
  
! 	switch (*str)
  	{
  		case 't':
  		case 'T':
! 			if (pg_strncasecmp(str, "true", len) == 0)
  				PG_RETURN_BOOL(true);
  			break;
  
  		case 'f':
  		case 'F':
! 			if (pg_strncasecmp(str, "false", len) == 0)
  				PG_RETURN_BOOL(false);
  			break;
  
  		case 'y':
  		case 'Y':
! 			if (pg_strncasecmp(str, "yes", len) == 0)
  				PG_RETURN_BOOL(true);
  			break;
  
  		case '1':
! 			if (pg_strncasecmp(str, "1", len) == 0)
  				PG_RETURN_BOOL(true);
  			break;
  
  		case 'n':
  		case 'N':
! 			if (pg_strncasecmp(str, "no", len) == 0)
  				PG_RETURN_BOOL(false);
  			break;
  
  		case '0':
! 			if (pg_strncasecmp(str, "0", len) == 0)
  				PG_RETURN_BOOL(false);
  			break;
  
*************** boolin(PG_FUNCTION_ARGS)
*** 77,83 ****
  
  	ereport(ERROR,
  			(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! 			 errmsg("invalid input syntax for type boolean: \"%s\"", b)));
  
  	/* not reached */
  	PG_RETURN_BOOL(false);
--- 92,98 ----
  
  	ereport(ERROR,
  			(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! 			 errmsg("invalid input syntax for type boolean: \"%s\"", in_str)));
  
  	/* not reached */
  	PG_RETURN_BOOL(false);
*************** boolsend(PG_FUNCTION_ARGS)
*** 127,132 ****
--- 142,178 ----
  	PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
  }
  
+ /*
+  *	    textbool			- cast function for text => bool
+  */
+ Datum
+ textbool(PG_FUNCTION_ARGS)
+ {
+ 	Datum 		 in_text = PG_GETARG_DATUM(0);
+ 	char 		*str;
+ 
+ 	str = DatumGetCString(DirectFunctionCall1(textout, in_text));
+ 
+ 	PG_RETURN_DATUM(DirectFunctionCall1(boolin, CStringGetDatum(str)));
+ }
+ 
+ /*
+  *	    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	30 May 2007 17:59:20 -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	30 May 2007 17:59:20 -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	30 May 2007 17:59:20 -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	30 May 2007 18:57:52 -0000
*************** SELECT bool 't' AS true;
*** 18,24 ****
   t
  (1 row)
  
! SELECT bool 'f' AS false;
   false 
  -------
   f
--- 18,24 ----
   t
  (1 row)
  
! SELECT bool '   f           ' AS false;
   false 
  -------
   f
*************** SELECT bool 't' <> bool 'f' AS true;
*** 54,59 ****
--- 54,83 ----
   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 "
+ SELECT ''::text::boolean AS invalid;            -- error
+ ERROR:  invalid input syntax for type boolean: ""
  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	30 May 2007 18:56:49 -0000
*************** SELECT 1 AS one;
*** 14,20 ****
  
  SELECT bool 't' AS true;
  
! SELECT bool 'f' AS false;
  
  SELECT bool 't' or bool 'f' AS true;
  
--- 14,20 ----
  
  SELECT bool 't' AS true;
  
! SELECT bool '   f           ' AS false;
  
  SELECT bool 't' or bool 'f' AS true;
  
*************** 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
+ SELECT ''::text::boolean AS invalid;            -- error
  
  CREATE TABLE BOOLTBL1 (f1 bool);
  
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to