Skip to content

Commit f086be3

Browse files
author
Neil Conway
committed
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.
1 parent bd0a260 commit f086be3

File tree

8 files changed

+110
-22
lines changed

8 files changed

+110
-22
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.202 2007/05/29 04:58:43 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.203 2007/06/01 23:40:18 neilc Exp $ -->
22

33
<chapter id="datatype">
44
<title id="datatype-title">Data Types</title>
@@ -2403,9 +2403,9 @@ January 8 04:05:06 1999 PST
24032403
<member><literal>'no'</literal></member>
24042404
<member><literal>'0'</literal></member>
24052405
</simplelist>
2406-
Using the key words <literal>TRUE</literal> and
2407-
<literal>FALSE</literal> is preferred (and
2408-
<acronym>SQL</acronym>-compliant).
2406+
Leading and trailing whitespace is ignored. Using the key words
2407+
<literal>TRUE</literal> and <literal>FALSE</literal> is preferred
2408+
(and <acronym>SQL</acronym>-compliant).
24092409
</para>
24102410

24112411
<example id="datatype-boolean-example">

‎src/backend/utils/adt/bool.c

Lines changed: 57 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,15 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/bool.c,v 1.38 2007/01/05 22:19:40 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/bool.c,v 1.39 2007/06/01 23:40:18 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
1515

1616
#include "postgres.h"
1717

18+
#include <ctype.h>
19+
1820
#include "libpq/pqformat.h"
1921
#include "utils/builtins.h"
2022

@@ -33,41 +35,54 @@
3335
Datum
3436
boolin(PG_FUNCTION_ARGS)
3537
{
36-
char *b = PG_GETARG_CSTRING(0);
37-
38-
switch (*b)
38+
const char *in_str = PG_GETARG_CSTRING(0);
39+
const char *str;
40+
size_t len;
41+
42+
/*
43+
* Skip leading and trailing whitespace
44+
*/
45+
str = in_str;
46+
while (isspace((unsigned char) *str))
47+
str++;
48+
49+
len = strlen(str);
50+
while (len > 0 && isspace((unsigned char) str[len - 1]))
51+
len--;
52+
53+
switch (*str)
3954
{
4055
case 't':
4156
case 'T':
42-
if (pg_strncasecmp(b, "true", strlen(b)) == 0)
57+
if (pg_strncasecmp(str, "true", len) == 0)
4358
PG_RETURN_BOOL(true);
4459
break;
4560

4661
case 'f':
4762
case 'F':
48-
if (pg_strncasecmp(b, "false", strlen(b)) == 0)
63+
if (pg_strncasecmp(str, "false", len) == 0)
4964
PG_RETURN_BOOL(false);
5065
break;
5166

5267
case 'y':
5368
case 'Y':
54-
if (pg_strncasecmp(b, "yes", strlen(b)) == 0)
69+
if (pg_strncasecmp(str, "yes", len) == 0)
5570
PG_RETURN_BOOL(true);
5671
break;
5772

5873
case '1':
59-
if (pg_strncasecmp(b, "1", strlen(b)) == 0)
74+
if (pg_strncasecmp(str, "1", len) == 0)
6075
PG_RETURN_BOOL(true);
6176
break;
6277

6378
case 'n':
6479
case 'N':
65-
if (pg_strncasecmp(b, "no", strlen(b)) == 0)
80+
if (pg_strncasecmp(str, "no", len) == 0)
6681
PG_RETURN_BOOL(false);
6782
break;
6883

6984
case '0':
70-
if (pg_strncasecmp(b, "0", strlen(b)) == 0)
85+
if (pg_strncasecmp(str, "0", len) == 0)
7186
PG_RETURN_BOOL(false);
7287
break;
7388

@@ -77,7 +92,7 @@ boolin(PG_FUNCTION_ARGS)
7792

7893
ereport(ERROR,
7994
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
80-
errmsg("invalid input syntax for type boolean: \"%s\"", b)));
95+
errmsg("invalid input syntax for type boolean: \"%s\"", in_str)));
8196

8297
/* not reached */
8398
PG_RETURN_BOOL(false);
@@ -127,6 +142,37 @@ boolsend(PG_FUNCTION_ARGS)
127142
PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
128143
}
129144

145+
/*
146+
* textbool - cast function for text => bool
147+
*/
148+
Datum
149+
textbool(PG_FUNCTION_ARGS)
150+
{
151+
Datum in_text = PG_GETARG_DATUM(0);
152+
char *str;
153+
154+
str = DatumGetCString(DirectFunctionCall1(textout, in_text));
155+
156+
PG_RETURN_DATUM(DirectFunctionCall1(boolin, CStringGetDatum(str)));
157+
}
158+
159+
/*
160+
* booltext - cast function for bool => text
161+
*/
162+
Datum
163+
booltext(PG_FUNCTION_ARGS)
164+
{
165+
bool arg1 = PG_GETARG_BOOL(0);
166+
char *str;
167+
168+
if (arg1)
169+
str = "true";
170+
else
171+
str = "false";
172+
173+
PG_RETURN_DATUM(DirectFunctionCall1(textin, CStringGetDatum(str)));
174+
}
175+
130176

131177
/*****************************************************************************
132178
* PUBLIC ROUTINES *

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.408 2007/06/01 15:33:19 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.409 2007/06/01 23:40:18 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200706011
56+
#define CATALOG_VERSION_NO 200706012
5757

5858
#endif

‎src/include/catalog/pg_cast.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
* Copyright (c) 2002-2007, PostgreSQL Global Development Group
1212
*
13-
* $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.32 2007/04/02 03:49:40 tgl Exp $
13+
* $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.33 2007/06/01 23:40:18 neilc Exp $
1414
*
1515
* NOTES
1616
* the genbki.sh script reads this file and generates .bki
@@ -302,6 +302,8 @@ DATA(insert ( 1700 25 1688 i ));
302302
DATA(insert ( 25 1700 1686 e ));
303303
DATA(insert ( 142 25 2922 e ));
304304
DATA(insert ( 25 142 2896 e ));
305+
DATA(insert ( 16 25 2971 e ));
306+
DATA(insert ( 25 16 2970 e ));
305307

306308
/*
307309
* Cross-category casts to and from VARCHAR
@@ -342,6 +344,8 @@ DATA(insert ( 1700 1043 1688 a ));
342344
DATA(insert ( 1043 1700 1686 e ));
343345
DATA(insert ( 142 1043 2922 e ));
344346
DATA(insert ( 1043 142 2896 e ));
347+
DATA(insert ( 16 1043 2971 e ));
348+
DATA(insert ( 1043 16 2970 e ));
345349

346350
/*
347351
* Cross-category casts to and from BPCHAR

‎src/include/catalog/pg_proc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.456 2007/05/21 17:10:29 petere Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.457 2007/06/01 23:40:18 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3221,6 +3221,10 @@ DESCR("List all files in a directory");
32213221
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_ ));
32223222
DESCR("Sleep for the specified time in seconds");
32233223

3224+
DATA(insert OID = 2970 ( boolean PGNSP PGUID 12 1 0 f f t f i 1 16 "25" _null_ _null_ _null_ textbool - _null_ ));
3225+
DESCR("text to boolean");
3226+
DATA(insert OID = 2971 ( text PGNSP PGUID 12 1 0 f f t f i 1 25 "16" _null_ _null_ _null_ booltext - _null_ ));
3227+
DESCR("boolean to text");
32243228

32253229
/* Aggregates (moved here from pg_aggregate for 7.3) */
32263230

‎src/include/utils/builtins.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.293 2007/05/17 23:31:49 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.294 2007/06/01 23:40:19 neilc Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -70,6 +70,8 @@ extern Datum boolin(PG_FUNCTION_ARGS);
7070
extern Datum boolout(PG_FUNCTION_ARGS);
7171
extern Datum boolrecv(PG_FUNCTION_ARGS);
7272
extern Datum boolsend(PG_FUNCTION_ARGS);
73+
extern Datum booltext(PG_FUNCTION_ARGS);
74+
extern Datum textbool(PG_FUNCTION_ARGS);
7375
extern Datum booleq(PG_FUNCTION_ARGS);
7476
extern Datum boolne(PG_FUNCTION_ARGS);
7577
extern Datum boollt(PG_FUNCTION_ARGS);

‎src/test/regress/expected/boolean.out

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ SELECT bool 't' AS true;
1818
t
1919
(1 row)
2020

21-
SELECT bool 'f' AS false;
21+
SELECT bool ' f ' AS false;
2222
false
2323
-------
2424
f
@@ -54,6 +54,30 @@ SELECT bool 't' <> bool 'f' AS true;
5454
t
5555
(1 row)
5656

57+
-- explicit casts to/from text
58+
SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false;
59+
true | false
60+
------+-------
61+
t | f
62+
(1 row)
63+
64+
SELECT ' true '::text::boolean AS true,
65+
' FALSE'::text::boolean AS false;
66+
true | false
67+
------+-------
68+
t | f
69+
(1 row)
70+
71+
SELECT true::boolean::text AS true, false::boolean::text AS false;
72+
true | false
73+
------+-------
74+
true | false
75+
(1 row)
76+
77+
SELECT ' tru e '::text::boolean AS invalid; -- error
78+
ERROR: invalid input syntax for type boolean: " tru e "
79+
SELECT ''::text::boolean AS invalid; -- error
80+
ERROR: invalid input syntax for type boolean: ""
5781
CREATE TABLE BOOLTBL1 (f1 bool);
5882
INSERT INTO BOOLTBL1 (f1) VALUES (bool 't');
5983
INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True');

‎src/test/regress/sql/boolean.sql

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@ SELECT 1 AS one;
1414

1515
SELECT bool 't' AS true;
1616

17-
SELECT bool 'f' AS false;
17+
SELECT bool ' f ' AS false;
1818

1919
SELECT bool 't' or bool 'f' AS true;
2020

@@ -26,6 +26,14 @@ SELECT bool 't' = bool 'f' AS false;
2626

2727
SELECT bool 't' <> bool 'f' AS true;
2828

29+
-- explicit casts to/from text
30+
SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false;
31+
SELECT ' true '::text::boolean AS true,
32+
' FALSE'::text::boolean AS false;
33+
SELECT true::boolean::text AS true, false::boolean::text AS false;
34+
35+
SELECT ' tru e '::text::boolean AS invalid; -- error
36+
SELECT ''::text::boolean AS invalid; -- error
2937

3038
CREATE TABLE BOOLTBL1 (f1 bool);
3139

0 commit comments

Comments
 (0)