Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gmtOD-0006xB-W2 for pgsql-hackers@arkaria.postgresql.org; Fri, 25 Jan 2019 04:46:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gmtOA-0008UJ-2n for pgsql-hackers@arkaria.postgresql.org; Fri, 25 Jan 2019 04:46:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gmtO9-0008Ts-3o for pgsql-hackers@lists.postgresql.org; Fri, 25 Jan 2019 04:46:45 +0000 Received: from bay.acenet.us ([173.230.252.50]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gmtNz-0007bv-KX for pgsql-hackers@lists.postgresql.org; Fri, 25 Jan 2019 04:46:43 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=anastigmatix.net; s=default; h=Content-Type:In-Reply-To:MIME-Version:Date: Message-ID:From:Cc:References:To:Subject:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=26iqpx27SaVgbs1O3Aw1yrzrdAxpxkp6HTW9lbqEJ0E=; b=eypyF3WI9Yhi0F9YrbvAAmPQv 9jBXcDwDjrespms4KiJEFw5eCtfMLbEJq4RqJGHYmtY0dW2bbMnpUmnRCWGB939NcEeyNSrhpPkc0 /VgigrV+cZ7Isu+ZCDHSBKUUtJh47JizJr9q+wRaWAO2ImuBTvPbEGkqjRdRXB4bZPlnSvZK2sGD6 +Z/Nm1AHyh0LBU1i3DIaYr+rLZmAHUfD29Ht9ulkYGf6FtvexTzLyKPvJFEbqsBjYhbB/sNrqqAa1 U2x+7CcFswnoqO4Vu9EdKVpoUd41U966jXzU0Re6QUi5fV648BDVnHNN/co+qptc1pg10msFkv8Ik V7SR++9Cw==; Received: from [192.182.57.242] (port=53776) by bay.acenet.us with esmtpsa (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.91) (envelope-from ) id 1gmtNt-004rZP-Ir; Thu, 24 Jan 2019 23:46:34 -0500 Subject: Re: PostgreSQL vs SQL/XML Standards To: Pavel Stehule References: <5C291741.9060506@anastigmatix.net> <5C297BB7.9070509@anastigmatix.net> <5C3A7A90.3070207@anastigmatix.net> <5C43FAEB.2070000@anastigmatix.net> <5C4401BE.4080606@anastigmatix.net> <5C449C71.7060903@anastigmatix.net> <5C44B332.6070609@anastigmatix.net> <5C44F26D.5010703@anastigmatix.net> Cc: PostgreSQL Hackers From: Chapman Flack X-Enigmail-Draft-Status: N1110 Message-ID: <5C4A94A5.8010402@anastigmatix.net> Date: Thu, 24 Jan 2019 23:46:29 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------010209090805050901000001" X-OutGoing-Spam-Status: No, score=-94.2 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - bay.acenet.us X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - anastigmatix.net X-Get-Message-Sender-Via: bay.acenet.us: authenticated_id: chap@anastigmatix.net X-Authenticated-Sender: bay.acenet.us: chap@anastigmatix.net X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------010209090805050901000001 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Hi, On 01/21/19 01:33, Pavel Stehule wrote: > ne 20. 1. 2019 23:13 odesílatel Chapman Flack > napsal: >> form (whether or not the word LATERAL is used), and re-executes xmltable >> whenever the referenced column value changes. In that case, whether the >> default argument is evaluated at function entry or later doesn't seem >> to matter: the function is re-executed, so evaluating the new default >> at the time of entry is sufficient. > > it has sense only for volatile functions. it was not often. On second hand > deferred evaluation shoul not be a problem, and more, it is evaluated only > when it is necessary, what is more sensible for me. That makes sense. I trimmed the language about input rows and referring to earlier columns, and put more emphasis on the usefulness of evaluating volatile functions only when needed. I am: - re-attaching xmltable-xpath-result-processing-bugfix-5.patch unchanged (just so CF app does not lose track) - re-attaching xmltable-xmlexists-passing-mechanisms-1.patch unchanged - attaching for the first time xml-functions-type-docfix-1.patch The doc patch is made to go on top of the passing-mechanisms patch (there were some doc changes in passing-mechanisms, changed again in the new patch to be links to the added Limits and Compatibility section). I hope the patched docs describe accurately what we have at this point. Regards, -Chap --------------010209090805050901000001 Content-Type: text/x-patch; name="xmltable-xpath-result-processing-bugfix-5.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="xmltable-xpath-result-processing-bugfix-5.patch" 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 ]]> diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c index 1cec168b2a..6d4ba50752 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,84 +4521,76 @@ 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. ). 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 = ""; + xmlChar *str; + + if (count > 1) + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + errmsg("more than one value returned by column XPath expression"))); + + str = xmlXPathCastNodeSetToString(xpathobj->nodesetval); + if (str) + cstr = copy_and_safe_free_xmlchar(str); + else + /* empty element */ + cstr = ""; } } + } + else if (xpathobj->type == XPATH_STRING) + { + /* Content should be escaped when target will be XML */ + if (typid == XMLOID) + cstr = escape_xml((char *) xpathobj->stringval); else - { - StringInfoData str; - int i; + cstr = (char *) xpathobj->stringval; - Assert(count > 1); + } + else if (xpathobj->type == XPATH_BOOLEAN) + { + char typcategory; + bool typispreferred; + 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"))); + /* Allow implicit casting from boolean to numbers */ + get_type_category_preferred(typid, &typcategory, &typispreferred); - /* Concatenate serialized values */ - initStringInfo(&str); - for (i = 0; i < count; i++) - { - appendStringInfoText(&str, - xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[i], - xtCxt->xmlerrcxt)); - } - cstr = str.data; - } + 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_STRING) + else if (xpathobj->type == XPATH_NUMBER) { - cstr = (char *) xpathobj->stringval; + 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..7b8da9283e 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 'a1aa2a bbbbxxxcccc' COLUMNS element text); - element -------------------- - a1aa2a bbbbcccc + element +---------------------- + a1aa2a bbbbxxxcccc (1 row) SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail @@ -1493,3 +1493,35 @@ 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' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); + a | b | c | d +----------+---+----+--- + a | a | hi | t +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); + a | b | c | d +----------+---+----+--- + a | a | hi | 1 +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); + x +--------------------------------------------------------------- + pre&deeppost +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/'); + x +---------------------------------------------------------------------- + pre&deeppost+ + +(1 row) + +SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '""', b xml PATH '""'); + a | b +--------+-------------- + | <foo/> +(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' 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' 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' 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' 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 'pre&deeppost' COLUMNS x xml PATH 'node()'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'prepre&deeppost' COLUMNS x xml PATH '/'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'prea1aa2a bbbbxxxcccc' COLUMNS element text); - element -------------------- - a1aa2a bbbbcccc + element +---------------------- + a1aa2a bbbbxxxcccc (1 row) SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail @@ -1473,3 +1473,35 @@ 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' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); + a | b | c | d +----------+---+----+--- + a | a | hi | t +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); + a | b | c | d +----------+---+----+--- + a | a | hi | 1 +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); + x +--------------------------------------------------------------- + pre&deeppost +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/'); + x +---------------------------------------------------------------------- + pre&deeppost+ + +(1 row) + +SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '""', b xml PATH '""'); + a | b +--------+-------------- + | <foo/> +(1 row) + diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql index 3b91b56d5a..8c3a34af81 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -595,3 +595,13 @@ INSERT INTO xmltest2 VALUES('2', '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' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/'); + +SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '""', b xml PATH '""'); + --------------010209090805050901000001 Content-Type: text/x-patch; name="xmltable-xmlexists-passing-mechanisms-1.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="xmltable-xmlexists-passing-mechanisms-1.patch" diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 06f3f69..df0fe4d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10583,7 +10583,7 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; -XMLEXISTS(text PASSING BY REF xml BY REF) +XMLEXISTS(text PASSING BY { REF | VALUE } xml BY { REF | VALUE }) @@ -10596,7 +10596,7 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; Example: TorontoOttawa'); +SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE 'TorontoOttawa'); xmlexists ------------ @@ -10606,14 +10606,22 @@ SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Tor - The BY REF clauses have no effect in - PostgreSQL, but are allowed for SQL conformance and compatibility - with other implementations. Per SQL standard, the - first BY REF is required, the second is - optional. Also note that the SQL standard specifies - the xmlexists construct to take an XQuery - expression as first argument, but PostgreSQL currently only - supports XPath, which is a subset of XQuery. + The BY REF or BY VALUE clauses + have no effect in PostgreSQL, but are allowed for compatibility + with other implementations. Per SQL standard, the one that precedes + any argument is required, and indicates the default for arguments that + follow, and one may follow any argument to override the default. + PostgreSQL ignores BY REF and passes by value always. + + + + In the SQL standard, an xmlexists construct first + appears in SQL:2006, evaluates an expression in the XML Query language, + allows passing values for named parameters in the expression as well + as for the context item, and does not require the passed values to be + documents, or even of XML type. In PostgreSQL, this construct currently + only evaluates an XPath 1.0 expression, and allows passing only one value, + which must be an XML document, to be the context item. @@ -10820,7 +10828,7 @@ SELECT xpath_exists('/my:a/text()', 'test xmltable( XMLNAMESPACES(namespace uri AS namespace name, ...), - row_expression PASSING BY REF document_expression BY REF + row_expression PASSING BY { REF | VALUE } document_expression BY { REF | VALUE } COLUMNS name { type PATH column_expression DEFAULT default_expression NOT NULL | NULL | FOR ORDINALITY } , ... @@ -10850,11 +10858,11 @@ SELECT xpath_exists('/my:a/text()', 'test document_expression provides the XML document to operate on. - The BY REF clauses have no effect in PostgreSQL, - but are allowed for SQL conformance and compatibility with other - implementations. The argument must be a well-formed XML document; fragments/forests are not accepted. + The BY REF or BY VALUE clauses are + accepted, as described for the xmlexists predicate, + but ignored; PostgreSQL currently passes XML by value always. diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index aeb262a..bade0fe 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -635,8 +635,8 @@ X204 XMLQuery: initializing an XQuery variable NO X205 XMLQuery: EMPTY ON EMPTY option NO X206 XMLQuery: NULL ON EMPTY option NO X211 XML 1.1 support NO -X221 XML passing mechanism BY VALUE NO -X222 XML passing mechanism BY REF YES +X221 XML passing mechanism BY VALUE YES +X222 XML passing mechanism BY REF NO parser accepts BY REF but ignores it; passing is always BY VALUE X231 XML(CONTENT(UNTYPED)) type NO X232 XML(CONTENT(ANY)) type NO X241 RETURNING CONTENT in XML publishing NO diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c086235..1b303d7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -13942,20 +13942,25 @@ xmlexists_argument: { $$ = $2; } - | PASSING c_expr BY REF + | PASSING c_expr xml_passing_mech { $$ = $2; } - | PASSING BY REF c_expr + | PASSING xml_passing_mech c_expr { - $$ = $4; + $$ = $3; } - | PASSING BY REF c_expr BY REF + | PASSING xml_passing_mech c_expr xml_passing_mech { - $$ = $4; + $$ = $3; } ; +xml_passing_mech: + BY REF + | BY VALUE_P + ; + /* * Aggregate decoration clauses --------------010209090805050901000001 Content-Type: text/x-patch; name="xml-functions-type-docfix-1.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="xml-functions-type-docfix-1.patch" diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index ed0ee58..f753105 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4269,16 +4269,21 @@ SET XML OPTION { DOCUMENT | CONTENT }; SET xmloption TO { DOCUMENT | CONTENT }; The default is CONTENT, so all forms of XML - data are allowed. + data are allowed except as noted below. - With the default XML option setting, you cannot directly cast - character strings to type xml if they contain a - document type declaration, because the definition of XML content - fragment does not accept them. If you need to do that, either - use XMLPARSE or change the XML option. + In the SQL:2006 and later standard, the CONTENT form + is a proper superset of the DOCUMENT form, and so the + default XML option setting would allow casting to XML from character + strings in either form. PostgreSQL, however, + uses the SQL:2003 definition in which CONTENT form + cannot contain a document type declaration. Therefore, there is no one + setting of the XML option that will allow casting to XML from every valid + character string. The default will work almost always, but for a document + with a DTD, it will be necessary to change the XML option or + use XMLPARSE specifying DOCUMENT. diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index df0fe4d..74a0818 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10140,8 +10140,13 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple + XML Functions + + XML Functions + + The functions and function-like expressions described in this section operate on values of type xml. Check . The particular behavior for individual data types is expected to evolve in order to align the - SQL and PostgreSQL data types with the XML Schema specification, - at which point a more precise description will appear. + PostgreSQL mappings with those specified in SQL:2006 and later, + as discussed in . @@ -10587,10 +10592,12 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; - The function xmlexists returns true if the - XPath expression in the first argument returns any nodes, and - false otherwise. (If either argument is null, the result is - null.) + The function xmlexists evaluates an XPath 1.0 + expression (the first argument), with the passed value as its context item. + The function returns false if the result of that evaluation yields an + empty nodeset, true if it yields any other value. (The function returns + null if an argument is null.) A nonnull value passed as the context item + must be an XML document, not a content fragment or any non-XML value. @@ -10607,21 +10614,12 @@ SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE 'T The BY REF or BY VALUE clauses - have no effect in PostgreSQL, but are allowed for compatibility - with other implementations. Per SQL standard, the one that precedes - any argument is required, and indicates the default for arguments that - follow, and one may follow any argument to override the default. - PostgreSQL ignores BY REF and passes by value always. - - - + are accepted in PostgreSQL, but ignored, as + discussed in . In the SQL standard, an xmlexists construct first - appears in SQL:2006, evaluates an expression in the XML Query language, - allows passing values for named parameters in the expression as well - as for the context item, and does not require the passed values to be - documents, or even of XML type. In PostgreSQL, this construct currently - only evaluates an XPath 1.0 expression, and allows passing only one value, - which must be an XML document, to be the context item. + appears in SQL:2006 and evaluates an expression in the XML Query language, + but this implementation allows only an XPath 1.0 expression, as discussed + in . @@ -10727,12 +10725,12 @@ SELECT xml_is_well_formed_document('test The function xpath_exists is a specialized form of the xpath function. Instead of returning the - individual XML values that satisfy the XPath, this function returns a - Boolean indicating whether the query was satisfied or not. This - function is equivalent to the standard XMLEXISTS predicate, + individual XML values that satisfy the XPath 1.0 expression, this function + returns a Boolean indicating whether the query was satisfied or not + (specifically, whether it produced any value other than an empty nodeset). + This function is equivalent to the XMLEXISTS predicate, except that it also offers support for a namespace mapping argument. @@ -10837,8 +10836,8 @@ SELECT xpath_exists('/my:a/text()', 'test The xmltable function produces a table based - on the given XML value, an XPath filter to extract rows, and an - optional set of column definitions. + on the given XML value, an XPath filter to extract rows, and a + set of column definitions. @@ -10849,30 +10848,33 @@ SELECT xpath_exists('/my:a/text()', 'test - The required row_expression argument is an XPath - expression that is evaluated against the supplied XML document to - obtain an ordered sequence of XML nodes. This sequence is what - xmltable transforms into output rows. + The required row_expression argument is + an XPath 1.0 expression that is evaluated, passing the + document_expression as its context item, to + obtain a set of XML nodes. These nodes are what + xmltable transforms into output rows. (No rows + will be produced if the document_expression + is null, or the row_expression produces an + empty nodeset, or any value other than a nodeset.) - document_expression provides the XML document to - operate on. - The argument must be a well-formed XML document; fragments/forests - are not accepted. + document_expression provides the context item + for the row_expression. It must be a well-formed + XML document; fragments/forests are not accepted. The BY REF or BY VALUE clauses are - accepted, as described for the xmlexists predicate, - but ignored; PostgreSQL currently passes XML by value always. + accepted but ignored, as discussed in + . + In the SQL standard, an xmltable construct first + appears in SQL:2006 and evaluates expressions in the XML Query language, + but this implementation allows only XPath 1.0 expressions, as discussed + in . The mandatory COLUMNS clause specifies the list of columns in the output table. - If the COLUMNS clause is omitted, the rows in the result - set contain a single column of type xml containing the - data matched by row_expression. - If COLUMNS is specified, each entry describes a - single column. + Each entry describes a single column. See the syntax summary above for the format. The column name and type are required; the path, default and nullability clauses are optional. @@ -10886,38 +10888,78 @@ SELECT xpath_exists('/my:a/text()', 'test - The column_expression for a column is an XPath expression - that is evaluated for each row, relative to the result of the - row_expression, to find the value of the column. + The column_expression for a column is an XPath 1.0 + expression + that is evaluated for each row, with the current node from the + row_expression result as its context item, + to find the value of the column. If no column_expression is given, then the column name is used as an implicit path. - If a column's XPath expression returns multiple elements, an error - is raised. - If the expression matches an empty tag, the result is an - empty string (not NULL). - Any xsi:nil attributes are ignored. + If a column's XPath expression returns a non-XML value (limited to + string, boolean, or double in XPath 1.0) and the column has a + PostgreSQL type other than xml, the column will be set + as if by assigning the value's string representation to the PostgreSQL + type (adjusting the "string representation" of a boolean to + 1 or 0 if the target column type + category is numeric, otherwise true or + false). - The text body of the XML matched by the column_expression - is used as the column value. Multiple text() nodes - within an element are concatenated in order. Any child elements, - processing instructions, and comments are ignored, but the text contents - of child elements are concatenated to the result. + If the column's expression returns a non-empty set of XML nodes + and the target column's type is xml, the column will + be assigned the expression result exactly, if it is of document or + content form. + + + A result containing more than one element node at the top level, or + non-whitespace text outside of an element, is an example of content form. + An XPath result can be of neither form, for example if it returns an + attribute node selected from the element that contains it. Such a result + will be put into content form with each such disallowed node replaced by + its string value, as defined for the XPath 1.0 + string function. + + + + + + A non-XML result assigned to an xml output column produces + content, a single text node with the string value of the result. + An XML result assigned to a column of any other type may not have more than + one node, or an error is raised. If there is exactly one node, the column + will be set as if by assigning the node's string + value (as defined for the XPath 1.0 string function) + to the PostgreSQL type. + + + + The string value of an XML element is the concatenation, in document order, + of all text nodes contained in that element and its descendants. The string + value of an element with no descendant text nodes is an + empty string (not NULL). + Any xsi:nil attributes are ignored. Note that the whitespace-only text() node between two non-text elements is preserved, and that leading whitespace on a text() node is not flattened. + The XPath 1.0 string function may be consulted for the + rules defining the string value of other XML node types and non-XML values. - If the path expression does not match for a given row but - default_expression is specified, the value resulting - from evaluating that expression is used. - If no DEFAULT clause is given for the column, - the field will be set to NULL. + The conversion rules presented here are not exactly those of the SQL + standard, as discussed in . + + + + If the path expression returns an empty nodeset + (typically, when it does not match) + for a given row, the column will be set to NULL, unless + a default_expression is specified; then the + value resulting from evaluating that expression is used. It is possible for a default_expression to reference the value of output columns that appear prior to it in the column list, so the default of one column may be based on the value of another @@ -10933,20 +10975,14 @@ SELECT xpath_exists('/my:a/text()', 'test - Unlike regular PostgreSQL functions, column_expression - and default_expression are not evaluated to a simple - value before calling the function. - column_expression is normally evaluated - exactly once per input row, and default_expression - is evaluated each time a default is needed for a field. - If the expression qualifies as stable or immutable the repeat + A default_expression, rather than being + evaluated immediately when xmltable is called, + is evaluated each time a default is needed for the column. + If the expression qualifies as stable or immutable, the repeat evaluation may be skipped. - Effectively xmltable behaves more like a subquery than a - function call. This means that you can usefully use volatile functions like - nextval in default_expression, and - column_expression may depend on other parts of the - XML document. + nextval in + default_expression. @@ -11294,6 +11330,301 @@ table2-mapping ]]> + + + Limits and Compatibility + + + XML Functions + limits and compatibility + + + + Queries restricted to XPath 1.0 + + + The PostgreSQL-specific functions + xpath and xpath_exists query + XML documents using the XPath language, and + PostgreSQL also provides XPath-only variants of + the standard functions XMLEXISTS and + XMLTABLE, which are defined in the SQL standard to use + the XQuery language. For all of these functions, + PostgreSQL relies on the + libxml library, which provides only XPath 1.0. + + + + There is a strong connection between the XQuery language and XPath versions + 2.0 and later: any expression that is syntactically valid and executes + successfully in both produces the same result (with a minor exception for + expressions containing numeric character references or predefined entity + references, which XQuery replaces with the corresponding character while + XPath leaves them alone). But there is no such connection between XPath 1.0 + and XQuery or the later XPath versions; it was an earlier language and + differs in many respects. + + + + There are two categories of limitation to keep in mind: the restriction + from XQuery to XPath for the functions specified in the SQL standard, and + the restriction of XPath to version 1.0 for both the standard and the + PostgreSQL-specific functions. + + + + Restriction of XQuery to XPath + + + Features of XQuery beyond those of XPath include: + + + + + XQuery expressions can construct and return new XML nodes, in addition + to all possible XPath values. XPath can introduce and return values of + the atomic types (numbers, strings, and so on) but can only return XML + nodes already present in documents supplied as input to the expression. + + + + + + XQuery has control constructs for iteration, sorting, and grouping. + + + + + + XQuery allows the declaration and use of local functions. + + + + + + + Recent XPath versions begin to offer overlapping capabilities + (such as the functional-style for-each and + sort, anonymous functions, and + parse-xml to create a node from a string), + but these were not available before XPath 3.0. + + + + + Restriction of XPath to 1.0 + + + For developers familiar with XQuery and XPath 2.0 or later, or porting + queries from other systems, XPath 1.0 presents a number of differences to + contend with: + + + + + The fundamental type of an XQuery/XPath expression, the + sequence, which can contain XML nodes, atomic values, + or both, does not exist in XPath 1.0. A 1.0 expression can only produce + a nodeset (possibly empty, or with one XML node or more), or a single + atomic value. + + + + + + Unlike an XQuery/XPath sequence, which can contain any desired items + in any desired order, an XPath 1.0 nodeset has no guaranteed order and, + like any set, can have no member appear more than once. (The + libxml library does seem to always return + nodesets to PostgreSQL with their members + in the same relative order they had in the input document; it does not + commit to this behavior, and an XPath 1.0 expression cannot control + it.) + + + + + + While XQuery/XPath provides all of the types defined in XML Schema + and many operators and functions over those types, XPath 1.0 has only + nodesets and three atomic types, boolean, + double, and string. + + + + + + XPath 1.0 has no conditional operator. An XQuery/XPath expression + such as if ( hat ) then hat/@size else "no hat" + has no XPath 1.0 equivalent. + + + + + + XPath 1.0 has no ordering comparison operator for strings. Both + "cat" < "dog" and + "cat" > "dog" are false, because each is a + numeric comparison of two NaNs. In contrast, + = and != do compare the strings + as strings. + + + + + + XPath 1.0 blurs the distinction between + value comparisons and + general comparisons as XQuery/XPath define them. + Both sale/@hatsize = 7 and + sale/@customer = "alice" are existentially + quantified comparisons, true if there is any sale with the given value + for the attribute, but sale/@taxable = false() + is a value comparison to the + effective boolean value of a whole nodeset, + and true only if no sale has a taxable attribute + at all. + + + + + + In the XQuery/XPath data model, a document node + can have either document form (exactly one top-level element, only + comments and processing instructions outside of it) or content form + (with those constraints relaxed). Its equivalent in XPath 1.0, the + root node, can only be in document form. + This is part of the reason an xml value passed as the + context item to any PostgreSQL XPath-based + function must be in document form. + + + + + + + The differences highlighted here are not all of them. In XQuery and + the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility + mode, and the W3C lists of function library + + + + + + and language + + + + + + changes applied in that mode offer a more complete account of the + differences, if still not exhaustive; the compatibility mode cannot make + the later languages exactly equivalent to XPath 1.0. + + + + + Mappings between SQL and XML data types and values + + + In SQL:2006 and later, both directions of conversion between standard SQL + data types and the XML Schema types are specified precisely. However, the + rules are expressed using the types and semantics of XQuery/XPath, and + have no direct application to the different data model of XPath 1.0. + + + + When PostgreSQL maps SQL data values to XML + (as in xmlelement), or XML to SQL (as in the output + columns of xmltable), except for the few cases + treated specially, PostgreSQL simply assumes + that the XML data type's XPath 1.0 string form will be valid as the + text-input form of the SQL datatype, and conversely. This rule has the + virtue of simplicity while producing, for many data types, results similar + to the mappings specified in the standard. + + + + Where interoperability with other systems is a concern, for some data + types, it may be necessary to use available data type formatting functions + (such as those in ) explicitly in + queries to produce the standard mappings. + + + + + + + Incidental limits of the implementation + + + + This section concerns limits that are not inherent in the + libxml library, but apply to the current + implementation in PostgreSQL. + + + + Only <literal>BY VALUE</literal> passing mechanism supported + + + The SQL standard defines two passing mechanisms + that apply when passing an XML argument from SQL to an XML function or + receiving a result: BY REF, in which a particular XML + value retains its node identity, and BY VALUE, in which + the content of the XML is passed but node identity is not preserved. A + mechanism can be specified before a list of parameters, as the default + mechanism for all of them, or after any parameter, to override the + default. + + + + To illustrate the difference, if + x is an XML value, these two queries in + an SQL:2006 environment would produce true and false, respectively: + + + + + + PostgreSQL will accept either + BY VALUE or BY REF in an + XMLEXISTS or XMLTABLE construct, + but ignores them; the xml data type holds a character-string + serialized representation, so there is no node identity to preserve, + and passing is always BY VALUE. + + + + + Cannot pass named parameters to queries + + + The XPath-based functions support passing one parameter to serve as the + XPath expression's context item, but do not support passing additional + values to be available to the expression as named parameters. + + + + + No <type>XML(SEQUENCE)</type> type + + + The PostgreSQL xml can only hold + a value in DOCUMENT or CONTENT form. + An XQuery/XPath expression context item must be a single XML node + or atomic value, while XPath 1.0 further restricts it to only an XML node, + and has no node type allowing CONTENT. The upshot is + that a well-formed DOCUMENT is the only form of XML + value that PostgreSQL can supply as an XPath + context item. + + + + diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index bade0fe..e947613 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -569,7 +569,7 @@ X056 Advanced table mapping: data mapping YES X057 Advanced table mapping: metadata mapping YES X058 Advanced table mapping: base64 encoding of binary strings YES X059 Advanced table mapping: hex encoding of binary strings YES -X060 XMLParse: character string input and CONTENT option YES +X060 XMLParse: character string input and CONTENT option YES uses SQL:2003 definition, so a document with a DTD cannot be parsed as CONTENT X061 XMLParse: character string input and DOCUMENT option YES X065 XMLParse: BLOB input and CONTENT option NO X066 XMLParse: BLOB input and DOCUMENT option NO @@ -593,7 +593,7 @@ X085 Predefined namespace prefixes NO X086 XML namespace declarations in XMLTable NO X090 XML document predicate YES X091 XML content predicate NO -X096 XMLExists NO XPath only +X096 XMLExists NO XPath 1.0 only X100 Host language support for XML: CONTENT option NO X101 Host language support for XML: DOCUMENT option NO X110 Host language support for XML: VARCHAR mapping NO @@ -661,11 +661,11 @@ X282 XMLValidate with CONTENT option NO X283 XMLValidate with SEQUENCE option NO X284 XMLValidate: NAMESPACE without ELEMENT clause NO X286 XMLValidate: NO NAMESPACE with ELEMENT clause NO -X300 XMLTable NO XPath only +X300 XMLTable NO XPath 1.0 only X301 XMLTable: derived column list option YES X302 XMLTable: ordinality column option YES X303 XMLTable: column default option YES -X304 XMLTable: passing a context item YES +X304 XMLTable: passing a context item YES must be XML DOCUMENT X305 XMLTable: initializing an XQuery variable NO X400 Name and identifier mapping YES X410 Alter column data type: XML type YES --------------010209090805050901000001--