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
