po 29. 10. 2018 v 10:11 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:
> Hi
>
> čt 25. 10. 2018 v 21:47 odesílatel Alvaro Herrera <
> alvhe...@2ndquadrant.com> napsal:
>
>> On 2018-Oct-25, Pavel Stehule wrote:
>>
>> > I am thinking so I can fix some issues related to XMLTABLE. Please,
>> send me
>> > more examples and test cases.
>>
>> Please see Markus Winand's patch that I referenced upthread.
>>
>
> here is a fix of some XMLTABLE mentioned issues.
>
this update allows cast boolean to numeric types from XPath expressions
Regards
Pavel
> Regards
>
> Pavel
>
>
>
>>
>> --
>> Álvaro Herrera https://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 37d85f71f3..a691dbf965 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -4569,6 +4569,41 @@ XmlTableGetValue(TableFuncScanState *state, int colnum,
{
cstr = (char *) xpathobj->stringval;
}
+ else if (xpathobj->type == XPATH_BOOLEAN)
+ {
+ char typcategory;
+ bool typispreferred;
+
+ /* Allow implicit casting from boolean to numbers */
+ get_type_category_preferred(typid, &typcategory, &typispreferred);
+
+ if (typcategory != TYPCATEGORY_NUMERIC)
+ cstr = xpathobj->boolval ? "true" : "false";
+ else
+ cstr = xpathobj->boolval ? "1" : "0";
+ }
+ else if (xpathobj->type == XPATH_NUMBER)
+ {
+ double fval = xpathobj->floatval;
+
+ switch (xmlXPathIsInf(fval))
+ {
+ case 1:
+ cstr = "Infinity";
+ break;
+ case -1:
+ cstr = "-Infinity";
+ break;
+ default:
+ if (xmlXPathIsNaN(fval))
+ cstr = "NaN";
+ else if (fval == 0.0)
+ cstr = "0";
+ else
+ cstr = psprintf("%0g", fval);
+
+ }
+ }
else
elog(ERROR, "unexpected XPath object type %u", xpathobj->type);
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 6e1f885112..4eb39f6169 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1493,3 +1493,16 @@ SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c
14
(4 rows)
+-- XPath result can be boolean or number too
+SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"');
+ a | b | c | d
+----------+---+----+---
+ <a>a</a> | a | hi | t
+(1 row)
+
+SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)');
+ a | b | c | d
+----------+---+----+---
+ <a>a</a> | a | hi | 1
+(1 row)
+
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 3b91b56d5a..1128ddb90f 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -595,3 +595,7 @@ INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D');
SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c');
SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
+
+-- XPath result can be boolean or number too
+SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"');
+SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)');
