docs-and-test-updates-to-xmltable-v9.patch
text/x-patch
Filename: docs-and-test-updates-to-xmltable-v9.patch
Type: text/x-patch
Part: 0
Message:
Re: patch: function xmltable
Patch
Format: format-patch
Series: patch v9
Subject: Docs and test updates to xmltable
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 81 | 8 |
| src/test/regress/sql/xml.sql | 20 | 2 |
From 1b0187063855519394139d9b88b2d9d696adb201 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Fri, 23 Sep 2016 16:06:51 +0800
Subject: [PATCH] Docs and test updates to xmltable
---
doc/src/sgml/func.sgml | 89 ++++++++++++++++++++++++++++++++++++++++----
src/test/regress/sql/xml.sql | 22 ++++++++++-
2 files changed, 101 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f6bead6..3292fcd 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10368,7 +10368,7 @@ SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuf
</ROWS>
]]></screen>
- following query produces result:
+ the following query produces the result:
<screen><![CDATA[
SELECT xmltable.*
@@ -10438,7 +10438,8 @@ SELECT *
<literal>BY REF</literal> is required, the second is optional.
Passing <literal>BY VALUE</> is not supported. Multiple
comma-separated terms in the PASSING clause are not supported.
- <literal>AS</> aliases are not supported.
+ <literal>AS</> aliases are not supported. The argument must be
+ a well-formed XML document; fragments/forests are not accepted.
</para>
<para>
@@ -10473,16 +10474,73 @@ SELECT *
<para>
The <literal>PATH</> for a column is an xpath expression that is
evaluated for each row, relative to the result of the
- <replaceable>rowexpr</>, to find the value of the column. If no
- <literal>PATH</> is given then the column name is used as an
- implicit path.
+ <replaceable>rowexpr</>, to find the value of the column.
+ If no <literal>PATH</> is given then the column name is used as an
+ implicit path. It is possible for a <literal>PATH</> expression to
+ reference output columns that appear before it in the column-list, so
+ paths may be dynamically constructed based on other parts of the XML
+ document:
+ <programlisting>
+
+ </programlisting>
+ <para>
+
+ <para>
+ The text body of the XML matched by the <literal>PATH</> expression is
+ used as the column value. Multiple <literal>text()</literal> 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:
+ <programlisting><![CDATA[
+SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--x--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
+element
+-----------------
+ a1aa2a bbbbcccc
+ (1 row)
+]]></programlisting>
+ Note that the whitespace-only <literal>text()</> node between two non-text
+ elements is preserved, and that leading whitespace on a <literal>text()</>
+ node is not flattened.
+ </para>
+
+ <para>
+ A <literal>PATH</> expression that matches multiple top-level elements
+ results in an error:
+ <programlisting><![CDATA[
+SELECT * FROM xmltable('/root' passing '<root><element>1</element><element>2</element></root>' COLUMNS element text);
+ERROR: more than one value returned by column XPath expression
+]]></programlisting>
+ This applies to <literal>text()</> nodes too, so avoid using explicit text nodes
+ in your path expressions unless this behaviour is desired:
+ <programlisting><![CDATA[
+SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a</element></root>' COLUMNS element text PATH 'element/text()');
+ERROR: more than one value returned by column XPath expression
+]]></programlisting>
+ If the <literal>PATH</> matches an empty tag the result is an empty string
+ (not <literal>NULL</>). Any <literal>xsi:nil</> attributes are ignored.
+ </para>
+
+ <para>
+ The five predefined XML entities <literal>'apos;</>,
+ <literal>'quot;</>, <literal>'amp;</>, <literal>'lt;</>
+ and <literal>'gt;</> are expanded to their corresponding characters
+ <literal>'</>, <literal>"</>, <literal>&</>, <literal><</>
+ and <literal>></> on processing. If the output column format is
+ <type>xml</> then the characters <literal><</>, <literal>></> and
+ <literal>&</> are converted to XML entities, but <literal>"</> and
+ <literal>'</> are left unchanged. This means that <literal>'apos;</>
+ and <literal>'quot;</> entities in XML input get expanded in xml
+ output columns. There is no way to prevent their expansion.
</para>
<para>
If the path expression does not match for a given row but a
<literal>DEFAULT</> expression is specified, the resulting
default value is used. If no <literal>DEFAULT</> is given the
- field will be <literal>NULL</>.
+ field will be <literal>NULL</>. Unlike normal queries, it is possible for
+ a <literal>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 column.
</para>
<para>
@@ -10492,6 +10550,22 @@ SELECT *
to null then the function terminates with an ERROR.
</para>
+ <important>
+ <para>
+ Unlike normal PostgreSQL functions, the <literal>PATH</> and
+ <literal>DEFAULT</> arguments to <function>xmltable</> are not evaluated
+ to a simple value before calling the function. <literal>PATH</>
+ expressions are normally evaluated <emphasis>exactly once per result row
+ </emphasis>, and <literal>DEFAULT</> expressions each time a default is
+ needed for a field. If the expression qualifies as stable or immutable
+ the repeat evaluation may be skipped. Effectively <function>xmltable</>
+ behaves more like a subquery than a function call. This means that you
+ can usefully use volatile functions like <function>nextval</> in
+ <literal>DEFAULT</> expressions, your <literal>PATH</> expressions may
+ depend on other parts of the XML document, etc.
+ </para>
+ </important>
+
<para>
A column marked with the
<literal>FOR ORDINALITY</literal> keyword will be populated with
@@ -10502,8 +10576,7 @@ SELECT *
<note>
<para>
- Empty tag is translated as empty string (possible attribute xsi:nil
- has not any effect. The XPath expression in PATH clause is evaluated
+ The XPath expression in PATH clause is evaluated
for any input row. The expression in DEFAULT expression is evaluated for
any missing value (for any output row).
</para>
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index b67af31..60529fc 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -393,8 +393,26 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.');
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*');
-SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?>bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
-SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?>bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text PATH 'element/text()'); -- should fail
+SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
+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
+
+-- XML builtin entities
+SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent text);
+SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent xml);
+
+-- Undefined entities (no DTD)
+SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent> </ent></a></x>' COLUMNS ent text);
+SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent> </ent></a></x>' COLUMNS ent xml);
+
+-- Defined entities (inline DTD)
+
+SELECT * FROM xmltable('/' PASSING $XML$<?xml version="1.0" standalone="yes" ?>
+<!DOCTYPE foo [
+ <!ELEMENT foo (#PCDATA)>
+ <!ENTITY pg "PostgreSQL">
+]>
+<foo>Hello &pg;.</foo>
+$XML$ COLUMNS foo text);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT xmltable.*
--
2.5.5