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
