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