Hi
čt 10. 1. 2019 v 14:00 odesílatel Arthur Zakirov <a.zaki...@postgrespro.ru>
napsal:
> Hello Pavel,
>
> On 09.11.2018 07:07, Pavel Stehule wrote:
> > I used your patch and append regress tests. I checked the result against
> > Oracle.
>
> I checked the patch with Chap cases. The patch fixes handling of
> boolean, number types which mentioned in the wiki.
>
> I have a few comments related to the code and the documentation. I
> attached the patch, which fixes it.
>
> There is an example in the documentation:
>
> SELECT xmltable.*
> FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
> element
> ----------------------
> Hello2a2 bbbCC
>
> With the patch XMLTABLE returns different result now.
>
> copy_and_safe_free_xmlchar() function should be hid by #ifdef
> USE_LIBXML, otherwise I get an error if I build the Postgres without
> --with-libxml.
>
> There is a comment within XmlTableGetValue(). I changed it, mainly I
> used Markus patch from the related thread mentioned by Alvaro.
>
> Please see the changes in the patch.
>
I merged your changes, and fixed regress tests.
Thank you for patch
Regards
Pavel
> --
> Arthur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 90d67f1acf..06f3f69073 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10999,9 +10999,9 @@ $$ AS data;
SELECT xmltable.*
FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
- element
-----------------------
- Hello2a2 bbbCC
+ element
+-------------------------
+ Hello2a2 bbbxxxCC
]]></screen>
</para>
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 1cec168b2a..df7f0cc20d 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,37 @@ XmlTableFetchRow(TableFuncScanState *state)
#endif /* not USE_LIBXML */
}
+#ifdef 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;
+}
+#endif
+
/*
* XmlTableGetValue
* Return the value for column number 'colnum' for the current row. If
@@ -4475,9 +4506,9 @@ XmlTableGetValue(TableFuncScanState *state, int colnum,
/*
* There are four possible cases, depending on the number of nodes
* returned by the XPath expression and the type of the target column:
- * a) XPath returns no nodes. b) One node is returned, and column is
- * of type XML. c) One node, column type other than XML. d) Multiple
- * nodes are returned.
+ * a) XPath returns no nodes. b) The target type is XML (return all
+ * as XML). For non-XML types: c) One node (return content).
+ * d) Multiple nodes (error).
*/
if (xpathobj->type == XPATH_NODESET)
{
@@ -4490,85 +4521,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/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index 0eba424346..62b83fb123 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1343,3 +1343,28 @@ SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c
---
(0 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"');
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml ...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)');
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml ...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH 'node()');
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH '/');
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
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 '/');
