čt 8. 11. 2018 v 15:18 odesílatel Markus Winand <markus.win...@winand.at> napsal:
> > > On 2018-11-6, at 15:23 , Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > > > > > > po 29. 10. 2018 v 11:45 odesílatel Pavel Stehule < > pavel.steh...@gmail.com> napsal: > > > > > > 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 > > > > Attached patch solves some cast issues mentioned by Chap. It solves > issue reported by Markus. I didn't use Markus's code, but it was > inspiration for me. I found native solution from libxml2. > > > > Regards > > > > Pavel > > Better than my patch. > > But I think the chunk in xml_xmlnodetoxmltype of my patch is still needed > — in one way or the other (see below). > > # select * from xmltable('*' PASSING '<e>pre<!--c1--><?pi > arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x XML PATH > 'node()'); > x > ----------------------------------------- > prec1arg&ent1<n2>&deep</n2>post > (1 row) > > Output is not the original XML. > > I dug a little further and found another case that doesn’t looks right > even with my change to xml_xmlnodetoxmltype applied: > > # select * from xmltable('*' PASSING '<e>pre<!--c1--><?pi > arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x XML PATH '/'); > x > --------------------------- > pre&ent1&deeppost > (1 row) > > Oracle gives in both cases XML. > > To fix that I included XML_DOCUMENT_NODE in the list of nodes that use > xmlNodeDump. Now I wonder if that logic should be reversed to use the > xmlXPathCastNodeToString branch in a few selected cases but default to the > branch xmlNodeDump for all other cases? > > I guess those few cases might be XML_ATTRIBUTE_NODE and XML_TEXT_NODE. > Regression tests are happy with that approach but I don’t think that proves > a lot. > > -markus > > diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c > index 37d85f7..7c1f884 100644 > --- a/src/backend/utils/adt/xml.c > +++ b/src/backend/utils/adt/xml.c > @@ -3682,7 +3682,7 @@ xml_xmlnodetoxmltype(xmlNodePtr cur, > PgXmlErrorContext *xmlerrcxt) > { > xmltype *result; > > - if (cur->type == XML_ELEMENT_NODE) > + if (cur->type != XML_ATTRIBUTE_NODE && cur->type != XML_TEXT_NODE) > { > xmlBufferPtr buf; > xmlNodePtr cur_copy; > > I used your patch and append regress tests. I checked the result against Oracle. Regards Pavel
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c index 37d85f71f3..7bed508e2a 100644 --- a/src/backend/utils/adt/xml.c +++ b/src/backend/utils/adt/xml.c @@ -3674,15 +3674,15 @@ SPI_sql_row_to_xmlelement(uint64 rownum, StringInfo result, char *tablename, #ifdef USE_LIBXML /* - * Convert XML node to text (dump subtree in case of element, - * return value otherwise) + * Convert XML node to text (dump subtree), for attribute and text + * returns escaped text. */ static text * xml_xmlnodetoxmltype(xmlNodePtr cur, PgXmlErrorContext *xmlerrcxt) { xmltype *result; - if (cur->type == XML_ELEMENT_NODE) + if (cur->type != XML_ATTRIBUTE_NODE && cur->type != XML_TEXT_NODE) { xmlBufferPtr buf; xmlNodePtr cur_copy; @@ -4427,6 +4427,35 @@ XmlTableFetchRow(TableFuncScanState *state) #endif /* not USE_LIBXML */ } +/* + * Copy XmlChar string to PostgreSQL memory. Ensure releasing of + * source xmllib string. + */ +static char * +copy_and_safe_free_xmlchar(xmlChar *str) +{ + char *result; + + if (str) + { + PG_TRY(); + { + result = pstrdup((char *) str); + } + PG_CATCH(); + { + xmlFree(str); + PG_RE_THROW(); + } + PG_END_TRY(); + xmlFree(str); + } + else + result = NULL; + + return result; +} + /* * XmlTableGetValue * Return the value for column number 'colnum' for the current row. If @@ -4490,85 +4519,72 @@ XmlTableGetValue(TableFuncScanState *state, int colnum, { *isnull = true; } - else if (count == 1 && typid == XMLOID) - { - text *textstr; - - /* simple case, result is one value */ - textstr = xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[0], - xtCxt->xmlerrcxt); - cstr = text_to_cstring(textstr); - } - else if (count == 1) + else { - xmlChar *str; - xmlNodePtr node; - - /* - * Most nodes (elements and even attributes) store their data - * in children nodes. If they don't have children nodes, it - * means that they are empty (e.g. <element/>). Text nodes and - * CDATA sections are an exception: they don't have children - * but have content in the Text/CDATA node itself. - */ - node = xpathobj->nodesetval->nodeTab[0]; - if (node->type != XML_CDATA_SECTION_NODE && - node->type != XML_TEXT_NODE) - node = node->xmlChildrenNode; - - str = xmlNodeListGetString(xtCxt->doc, node, 1); - if (str != NULL) + if (typid == XMLOID) { - PG_TRY(); - { - cstr = pstrdup((char *) str); - } - PG_CATCH(); + text *textstr; + StringInfoData str; + int i; + + /* Concatenate serialized values */ + initStringInfo(&str); + for (i = 0; i < count; i++) { - xmlFree(str); - PG_RE_THROW(); + textstr = + xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[i], + xtCxt->xmlerrcxt); + + appendStringInfoText(&str, textstr); } - PG_END_TRY(); - xmlFree(str); + cstr = str.data; } else { - /* Ensure mapping of empty tags to PostgreSQL values. */ - cstr = ""; - } - } - else - { - StringInfoData str; - int i; - - Assert(count > 1); + xmlChar *str; - /* - * When evaluating the XPath expression returns multiple - * nodes, the result is the concatenation of them all. The - * target type must be XML. - */ - if (typid != XMLOID) - ereport(ERROR, - (errcode(ERRCODE_CARDINALITY_VIOLATION), - errmsg("more than one value returned by column XPath expression"))); + if (count > 1) + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + errmsg("more than one value returned by column XPath expression"))); - /* Concatenate serialized values */ - initStringInfo(&str); - for (i = 0; i < count; i++) - { - appendStringInfoText(&str, - xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[i], - xtCxt->xmlerrcxt)); + str = xmlXPathCastNodeSetToString(xpathobj->nodesetval); + if (str) + cstr = copy_and_safe_free_xmlchar(str); + else + /* empty element */ + cstr = ""; } - cstr = str.data; } } else if (xpathobj->type == XPATH_STRING) { cstr = (char *) xpathobj->stringval; } + else if (xpathobj->type == XPATH_BOOLEAN) + { + char typcategory; + bool typispreferred; + xmlChar *str; + + /* Allow implicit casting from boolean to numbers */ + get_type_category_preferred(typid, &typcategory, &typispreferred); + + if (typcategory != TYPCATEGORY_NUMERIC) + str = xmlXPathCastBooleanToString(xpathobj->boolval); + else + str = xmlXPathCastNumberToString( + xmlXPathCastBooleanToNumber(xpathobj->boolval)); + + cstr = copy_and_safe_free_xmlchar(str); + } + else if (xpathobj->type == XPATH_NUMBER) + { + xmlChar *str; + + str = xmlXPathCastNumberToString(xpathobj->floatval); + cstr = copy_and_safe_free_xmlchar(str); + } 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..bfb98e3626 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -1210,9 +1210,9 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" (2 rows) SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text); - element -------------------- - a1aa2a bbbbcccc + element +---------------------- + a1aa2a bbbbxxxcccc (1 row) SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text PATH 'element/text()'); -- should fail @@ -1493,3 +1493,29 @@ 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) + +SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH 'node()'); + x +--------------------------------------------------------------- + pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH '/'); + x +---------------------------------------------------------------------- + <e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>+ + +(1 row) + diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql index 3b91b56d5a..7089fad0a1 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -595,3 +595,10 @@ 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(.)'); + +SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH 'node()'); +SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH '/');