From baeb096735f12b35be6fbf210aab6e45d8737573 Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Fri, 1 Jun 2007 23:40:19 +0000 Subject: [PATCH] Allow leading and trailing whitespace in the input to the boolean type. Also, add explicit casts between boolean and text/varchar. Both of these changes are for conformance with SQL:2003. Update the regression tests, bump the catversion. --- doc/src/sgml/datatype.sgml | 6 +-- src/backend/utils/adt/bool.c | 66 +++++++++++++++++++++++---- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_cast.h | 4 ++ src/include/catalog/pg_proc.h | 4 ++ src/include/utils/builtins.h | 2 + src/test/regress/expected/boolean.out | 26 ++++++++++- src/test/regress/sql/boolean.sql | 10 +++- 8 files changed, 104 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index ed6269217f..130b15e437 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2403,9 +2403,9 @@ January 8 04:05:06 1999 PST 'no' '0' - Using the key words TRUE and - FALSE is preferred (and - SQL-compliant). + Leading and trailing whitespace is ignored. Using the key words + TRUE and FALSE is preferred + (and SQL-compliant). diff --git a/src/backend/utils/adt/bool.c b/src/backend/utils/adt/bool.c index d46ca0c314..3c367fd7cd 100644 --- a/src/backend/utils/adt/bool.c +++ b/src/backend/utils/adt/bool.c @@ -15,6 +15,8 @@ #include "postgres.h" +#include + #include "libpq/pqformat.h" #include "utils/builtins.h" @@ -33,41 +35,54 @@ Datum boolin(PG_FUNCTION_ARGS) { - char *b = PG_GETARG_CSTRING(0); - - switch (*b) + 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(b, "true", strlen(b)) == 0) + if (pg_strncasecmp(str, "true", len) == 0) PG_RETURN_BOOL(true); break; case 'f': case 'F': - if (pg_strncasecmp(b, "false", strlen(b)) == 0) + if (pg_strncasecmp(str, "false", len) == 0) PG_RETURN_BOOL(false); break; case 'y': case 'Y': - if (pg_strncasecmp(b, "yes", strlen(b)) == 0) + if (pg_strncasecmp(str, "yes", len) == 0) PG_RETURN_BOOL(true); break; case '1': - if (pg_strncasecmp(b, "1", strlen(b)) == 0) + if (pg_strncasecmp(str, "1", len) == 0) PG_RETURN_BOOL(true); break; case 'n': case 'N': - if (pg_strncasecmp(b, "no", strlen(b)) == 0) + if (pg_strncasecmp(str, "no", len) == 0) PG_RETURN_BOOL(false); break; case '0': - if (pg_strncasecmp(b, "0", strlen(b)) == 0) + if (pg_strncasecmp(str, "0", len) == 0) PG_RETURN_BOOL(false); break; @@ -77,7 +92,7 @@ boolin(PG_FUNCTION_ARGS) ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), - errmsg("invalid input syntax for type boolean: \"%s\"", b))); + errmsg("invalid input syntax for type boolean: \"%s\"", in_str))); /* not reached */ PG_RETURN_BOOL(false); @@ -127,6 +142,37 @@ boolsend(PG_FUNCTION_ARGS) 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 * diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 0303bcf84e..f7403019af 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200706011 +#define CATALOG_VERSION_NO 200706012 #endif diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h index ae6d5c22eb..bde147df86 100644 --- a/src/include/catalog/pg_cast.h +++ b/src/include/catalog/pg_cast.h @@ -302,6 +302,8 @@ DATA(insert ( 1700 25 1688 i )); 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 @@ -342,6 +344,8 @@ DATA(insert ( 1700 1043 1688 a )); 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 diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 69b96a6046..a75baf9c18 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3221,6 +3221,10 @@ DESCR("List all files in a directory"); 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) */ diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 6e9df275e3..97f41c30c4 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -70,6 +70,8 @@ extern Datum boolin(PG_FUNCTION_ARGS); 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); diff --git a/src/test/regress/expected/boolean.out b/src/test/regress/expected/boolean.out index 93e475b673..511987e8b9 100644 --- a/src/test/regress/expected/boolean.out +++ b/src/test/regress/expected/boolean.out @@ -18,7 +18,7 @@ SELECT bool 't' AS true; t (1 row) -SELECT bool 'f' AS false; +SELECT bool ' f ' AS false; false ------- f @@ -54,6 +54,30 @@ SELECT bool 't' <> bool 'f' AS true; 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'); diff --git a/src/test/regress/sql/boolean.sql b/src/test/regress/sql/boolean.sql index df97dfab3b..c68f02e7a5 100644 --- a/src/test/regress/sql/boolean.sql +++ b/src/test/regress/sql/boolean.sql @@ -14,7 +14,7 @@ SELECT 1 AS one; SELECT bool 't' AS true; -SELECT bool 'f' AS false; +SELECT bool ' f ' AS false; SELECT bool 't' or bool 'f' AS true; @@ -26,6 +26,14 @@ SELECT bool 't' = bool 'f' AS false; 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); -- 2.39.5