xmltable-43.patch
text/x-patch
Patch
Format: unified
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 327 | 49 |
| src/backend/commands/explain.c | 24 | 0 |
| src/backend/executor/execAmi.c | 6 | 0 |
| src/backend/executor/execProcnode.c | 14 | 0 |
| src/backend/executor/Makefile | 2 | 1 |
| src/backend/executor/nodeTableFuncscan.c | 540 | 0 |
| src/backend/nodes/copyfuncs.c | 93 | 0 |
| src/backend/nodes/equalfuncs.c | 62 | 0 |
| src/backend/nodes/nodeFuncs.c | 66 | 0 |
| src/backend/nodes/outfuncs.c | 75 | 0 |
| src/backend/nodes/print.c | 4 | 0 |
| src/backend/nodes/readfuncs.c | 49 | 0 |
| src/backend/optimizer/path/allpaths.c | 40 | 0 |
| src/backend/optimizer/path/costsize.c | 81 | 0 |
| src/backend/optimizer/plan/createplan.c | 75 | 0 |
| src/backend/optimizer/plan/initsplan.c | 2 | 0 |
| src/backend/optimizer/plan/planner.c | 26 | 13 |
| src/backend/optimizer/plan/setrefs.c | 14 | 0 |
| src/backend/optimizer/plan/subselect.c | 6 | 0 |
| src/backend/optimizer/prep/prepjointree.c | 6 | 0 |
| src/backend/optimizer/util/pathnode.c | 26 | 0 |
| src/backend/optimizer/util/plancat.c | 4 | 2 |
| src/backend/optimizer/util/relnode.c | 1 | 0 |
| src/backend/parser/analyze.c | 9 | 0 |
| src/backend/parser/gram.y | 203 | 7 |
| src/backend/parser/parse_clause.c | 265 | 0 |
| src/backend/parser/parse_coerce.c | 26 | 7 |
| src/backend/parser/parse_expr.c | 1 | 0 |
| src/backend/parser/parse_relation.c | 69 | 1 |
| src/backend/parser/parse_target.c | 7 | 0 |
| src/backend/rewrite/rewriteHandler.c | 4 | 0 |
| src/backend/utils/adt/ruleutils.c | 128 | 0 |
| src/backend/utils/adt/xml.c | 559 | 7 |
| src/include/executor/nodeTableFuncscan.h | 24 | 0 |
| src/include/executor/tablefunc.h | 65 | 0 |
| src/include/nodes/execnodes.h | 29 | 0 |
| src/include/nodes/nodes.h | 5 | 0 |
| src/include/nodes/parsenodes.h | 40 | 0 |
| src/include/nodes/plannodes.h | 10 | 0 |
| src/include/nodes/primnodes.h | 24 | 0 |
| src/include/optimizer/cost.h | 5 | 0 |
| src/include/optimizer/pathnode.h | 4 | 0 |
| src/include/parser/kwlist.h | 3 | 0 |
| src/include/parser/parse_coerce.h | 4 | 0 |
| src/include/parser/parse_relation.h | 5 | 0 |
| src/include/utils/xml.h | 3 | 0 |
| src/test/regress/expected/xml_1.out | 488 | 0 |
| src/test/regress/expected/xml_2.out | 521 | 0 |
| src/test/regress/expected/xml.out | 521 | 0 |
| src/test/regress/sql/xml.sql | 293 | 0 |
| src/tools/pgindent/typedefs.list | 12 | 0 |
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d7738b18b7..4ceb8fe5d8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10325,50 +10325,53 @@ SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuf
<sect2 id="functions-xml-processing">
<title>Processing XML</title>
- <indexterm>
- <primary>XPath</primary>
- </indexterm>
-
<para>
To process values of data type <type>xml</type>, PostgreSQL offers
- the functions <function>xpath</function> and
+ the functions <function>xpath</function>,
<function>xpath_exists</function>, which evaluate XPath 1.0
- expressions.
+ expressions, and the <function>XMLTABLE</function> predicate.
</para>
+ <sect3 id="functions-xml-processing-xpath">
+ <title><literal>xpath</literal></title>
+
+ <indexterm>
+ <primary>XPath</primary>
+ </indexterm>
+
<synopsis>
<function>xpath</function>(<replaceable>xpath</replaceable>, <replaceable>xml</replaceable> <optional>, <replaceable>nsarray</replaceable></optional>)
</synopsis>
- <para>
- The function <function>xpath</function> evaluates the XPath
- expression <replaceable>xpath</replaceable> (a <type>text</> value)
- against the XML value
- <replaceable>xml</replaceable>. It returns an array of XML values
- corresponding to the node set produced by the XPath expression.
- If the XPath expression returns a scalar value rather than a node set,
- a single-element array is returned.
- </para>
+ <para>
+ The function <function>xpath</function> evaluates the XPath
+ expression <replaceable>xpath</replaceable> (a <type>text</> value)
+ against the XML value
+ <replaceable>xml</replaceable>. It returns an array of XML values
+ corresponding to the node set produced by the XPath expression.
+ If the XPath expression returns a scalar value rather than a node set,
+ a single-element array is returned.
+ </para>
- <para>
- The second argument must be a well formed XML document. In particular,
- it must have a single root node element.
- </para>
+ <para>
+ The second argument must be a well formed XML document. In particular,
+ it must have a single root node element.
+ </para>
- <para>
- The optional third argument of the function is an array of namespace
- mappings. This array should be a two-dimensional <type>text</> array with
- the length of the second axis being equal to 2 (i.e., it should be an
- array of arrays, each of which consists of exactly 2 elements).
- The first element of each array entry is the namespace name (alias), the
- second the namespace URI. It is not required that aliases provided in
- this array be the same as those being used in the XML document itself (in
- other words, both in the XML document and in the <function>xpath</function>
- function context, aliases are <emphasis>local</>).
- </para>
+ <para>
+ The optional third argument of the function is an array of namespace
+ mappings. This array should be a two-dimensional <type>text</> array with
+ the length of the second axis being equal to 2 (i.e., it should be an
+ array of arrays, each of which consists of exactly 2 elements).
+ The first element of each array entry is the namespace name (alias), the
+ second the namespace URI. It is not required that aliases provided in
+ this array be the same as those being used in the XML document itself (in
+ other words, both in the XML document and in the <function>xpath</function>
+ function context, aliases are <emphasis>local</>).
+ </para>
- <para>
- Example:
+ <para>
+ Example:
<screen><![CDATA[
SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
@@ -10378,10 +10381,10 @@ SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
{test}
(1 row)
]]></screen>
- </para>
+ </para>
- <para>
- To deal with default (anonymous) namespaces, do something like this:
+ <para>
+ To deal with default (anonymous) namespaces, do something like this:
<screen><![CDATA[
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
@@ -10391,27 +10394,31 @@ SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a
{test}
(1 row)
]]></screen>
- </para>
+ </para>
+ </sect3>
- <indexterm>
- <primary>xpath_exists</primary>
- </indexterm>
+ <sect3 id="functions-xml-processing-xpath-exists">
+ <title><literal>xpath_exists</literal></title>
+
+ <indexterm>
+ <primary>xpath_exists</primary>
+ </indexterm>
<synopsis>
<function>xpath_exists</function>(<replaceable>xpath</replaceable>, <replaceable>xml</replaceable> <optional>, <replaceable>nsarray</replaceable></optional>)
</synopsis>
- <para>
- The function <function>xpath_exists</function> is a specialized form
- of the <function>xpath</function> 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 <literal>XMLEXISTS</> predicate,
- except that it also offers support for a namespace mapping argument.
- </para>
+ <para>
+ The function <function>xpath_exists</function> is a specialized form
+ of the <function>xpath</function> 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 <literal>XMLEXISTS</> predicate,
+ except that it also offers support for a namespace mapping argument.
+ </para>
- <para>
- Example:
+ <para>
+ Example:
<screen><![CDATA[
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
@@ -10421,7 +10428,278 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m
t
(1 row)
]]></screen>
+ </para>
+ </sect3>
+
+ <sect3 id="functions-xml-processing-xmltable">
+ <title><literal>xmltable</literal></title>
+
+ <indexterm>
+ <primary>xmltable</primary>
+ </indexterm>
+
+<synopsis>
+<function>xmltable</function>(
+ <optional>xmlnamespaces(<replaceable>namespace uri</replaceable> AS <replaceable>namespace name</replaceable><optional>, ...</optional>)</optional>
+ <replaceable>rowexpr</replaceable> PASSING <optional>BY REF</optional> <replaceable>xml</replaceable> <optional>BY REF</optional>
+ <optional>COLUMNS <replaceable>name</replaceable> <replaceable>type</replaceable>
+ <optional>PATH <replaceable>columnexpr</replaceable></optional>
+ <optional>DEFAULT <replaceable>expr</replaceable></optional>
+ <optional>NOT NULL | NULL</optional> <optional>, ...</optional></optional>
+)
+</synopsis>
+
+ <para>
+ The <function>xmltable</function> produces a table based on the
+ passed XML value, an xpath filter to extract rows, and an
+ optional set of column definitions.
+ </para>
+
+ <para>
+ For XML document:
+ <screen><![CDATA[
+<ROWS>
+ <ROW id="1">
+ <COUNTRY_ID>AU</COUNTRY_ID>
+ <COUNTRY_NAME>Australia</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+ </ROW>
+ <ROW id="2">
+ <COUNTRY_ID>CN</COUNTRY_ID>
+ <COUNTRY_NAME>China</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+ </ROW>
+ <ROW id="3">
+ <COUNTRY_ID>HK</COUNTRY_ID>
+ <COUNTRY_NAME>HongKong</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+ </ROW>
+ <ROW id="4">
+ <COUNTRY_ID>IN</COUNTRY_ID>
+ <COUNTRY_NAME>India</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+ </ROW>
+ <ROW id="5">
+ <COUNTRY_ID>JP</COUNTRY_ID>
+ <COUNTRY_NAME>Japan</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+ </ROW>
+ <ROW id="6">
+ <COUNTRY_ID>SG</COUNTRY_ID>
+ <COUNTRY_NAME>Singapore</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
+ </ROW>
+</ROWS>
+]]></screen>
+
+ the following query produces the result:
+
+<screen><![CDATA[
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL xmltable('//ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ country_name text PATH 'COUNTRY_NAME',
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE[@unit = "km"]/text()',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+ id | country_name | country_id | region_id | size | unit | premier_name
+----+--------------+------------+-----------+------+------+---------------
+ 1 | Australia | AU | 3 | | | not specified
+ 2 | China | CN | 3 | | | not specified
+ 3 | HongKong | HK | 3 | | | not specified
+ 4 | India | IN | 3 | | | not specified
+ 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | Singapore | SG | 3 | 791 | km | not specified
+]]></screen>
+ </para>
+
+ <para>
+ The optional <literal>xmlnamespaces</> clause is a comma-separated list of
+ namespaces of the form <replaceable>namespace-URI</> <literal>AS</>
+ <replaceable>namespace-name</>. It specifies the XML namespaces used in
+ the document and their aliases. The default namespace is not supported
+ yet.
+
+<screen><![CDATA[
+SELECT *
+ FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS ns)
+ '/ns:rows/ns:row' PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'a');
+
+ a
+----
+ 10
+]]></screen>
</para>
+
+ <para>
+ The required <replaceable>rowexpr</> 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
+ <function>xmltable</> transforms into output rows.
+ </para>
+
+ <note>
+ <para>
+ The SQL/XML standard specifies the the
+ <function>xmltable</function> construct should take an XQuery
+ expression as the first argument, but PostgreSQL currently only
+ supports XPath, which is a subset of XQuery.
+ </para>
+ </note>
+
+ <para>
+ The <literal>PASSING</> <replaceable>xml</> clause provides the
+ XML document to operate on.
+ The <literal>BY REF</literal> clauses have no effect in
+ PostgreSQL, but are allowed for SQL conformance and compatibility
+ with other implementations. Per the SQL/XML standard, the first
+ <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. The argument must be
+ a well-formed XML document; fragments/forests are not accepted.
+ </para>
+
+ <para>
+ The optional <literal>COLUMNS</literal> clause specifies the list
+ of colums in the generated table. If the whole
+ <literal>COLUMNS</> list is omitted, then the result set is a
+ single column where each row is a field of <literal>xml</> type
+ containing the data matched by the <replaceable>rowexpr</>.
+ Otherwise each entry describes a single column. See the syntax
+ summary above for the format. The column name and type are
+ required; the path and not-null specifier are optional.
+ <screen><![CDATA[
+SELECT *
+ FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y' AS ns)
+ '/ns:rows/ns:row' PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>');
+
+ xmltable
+------------------------------
+ <a xmlns="http://x.y">10</a>
+]]></screen>
+ </para>
+
+ <note>
+ <para>
+ Column names and types are identifiers so unlike normal
+ function arguments they may not be specified as bind parameters,
+ column references, or expressions. The path and default are
+ ordinary values.
+ </para>
+ </note>
+
+ <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.
+ </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</>. 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>
+ Columns may be marked <literal>NOT NULL</>. If the xpath expression
+ for a <literal>NOT NULL</> column does not match anything and there
+ is no <literal>DEFAULT</> or the <literal>DEFAULT</> also evaluated
+ 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 input 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
+ row numbers that match the order in which the the output rows appeared
+ in the original input XML document. Only one column should be
+ marked <literal>FOR ORDINALITY</literal>.
+ </para>
+
+ <para>
+ Only XPath query language is supported. PostgreSQL doesn't support XQuery
+ language. Then the syntax of <function>xmltable</function> doesn't
+ allow to use XQuery related functionality - the name of xml expression
+ (clause <literal>AS</literal>) is not allowed, and only one xml expression
+ should be passed to <function>xmltable</function> function as parameter.
+ </para>
+ </sect3>
</sect2>
<sect2 id="functions-xml-mapping">
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c9e0a3e42d..328ebb6928 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -781,6 +781,7 @@ ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used)
case T_TidScan:
case T_SubqueryScan:
case T_FunctionScan:
+ case T_TableFuncScan:
case T_ValuesScan:
case T_CteScan:
case T_WorkTableScan:
@@ -926,6 +927,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_FunctionScan:
pname = sname = "Function Scan";
break;
+ case T_TableFuncScan:
+ pname = sname = "TableFunc Scan";
+ break;
case T_ValuesScan:
pname = sname = "Values Scan";
break;
@@ -1103,6 +1107,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_TidScan:
case T_SubqueryScan:
case T_FunctionScan:
+ case T_TableFuncScan:
case T_ValuesScan:
case T_CteScan:
case T_WorkTableScan:
@@ -1416,6 +1421,20 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
+ case T_TableFuncScan:
+ if (es->verbose)
+ {
+ TableFunc *tablefunc = ((TableFuncScan *) plan)->tablefunc;
+
+ show_expression((Node *) tablefunc,
+ "Table Function Call", planstate, ancestors,
+ es->verbose, es);
+ }
+ show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
+ if (plan->qual)
+ show_instrumentation_count("Rows Removed by Filter", 1,
+ planstate, es);
+ break;
case T_TidScan:
{
/*
@@ -2593,6 +2612,11 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
objecttag = "Function Name";
}
break;
+ case T_TableFuncScan:
+ Assert(rte->rtekind == RTE_TABLEFUNC);
+ objectname = "xmltable";
+ objecttag = "Table Function Name";
+ break;
case T_ValuesScan:
Assert(rte->rtekind == RTE_VALUES);
break;
diff --git a/src/backend/executor/Makefile b/src/backend/executor/Makefile
index 2a2b7eb9bd..a9893c2b22 100644
--- a/src/backend/executor/Makefile
+++ b/src/backend/executor/Makefile
@@ -26,6 +26,7 @@ OBJS = execAmi.o execCurrent.o execGrouping.o execIndexing.o execJunk.o \
nodeSamplescan.o nodeSeqscan.o nodeSetOp.o nodeSort.o nodeUnique.o \
nodeValuesscan.o nodeCtescan.o nodeWorktablescan.o \
nodeGroup.o nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o \
- nodeForeignscan.o nodeWindowAgg.o tstoreReceiver.o tqueue.o spi.o
+ nodeForeignscan.o nodeWindowAgg.o tstoreReceiver.o tqueue.o spi.o \
+ nodeTableFuncscan.o
include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c
index d3802079f5..5d59f95a91 100644
--- a/src/backend/executor/execAmi.c
+++ b/src/backend/executor/execAmi.c
@@ -48,6 +48,7 @@
#include "executor/nodeSort.h"
#include "executor/nodeSubplan.h"
#include "executor/nodeSubqueryscan.h"
+#include "executor/nodeTableFuncscan.h"
#include "executor/nodeTidscan.h"
#include "executor/nodeUnique.h"
#include "executor/nodeValuesscan.h"
@@ -198,6 +199,10 @@ ExecReScan(PlanState *node)
ExecReScanFunctionScan((FunctionScanState *) node);
break;
+ case T_TableFuncScanState:
+ ExecReScanTableFuncScan((TableFuncScanState *) node);
+ break;
+
case T_ValuesScanState:
ExecReScanValuesScan((ValuesScanState *) node);
break;
@@ -564,6 +569,7 @@ ExecMaterializesOutput(NodeTag plantype)
{
case T_Material:
case T_FunctionScan:
+ case T_TableFuncScan:
case T_CteScan:
case T_WorkTableScan:
case T_Sort:
diff --git a/src/backend/executor/execProcnode.c b/src/backend/executor/execProcnode.c
index 0dd95c6d17..651bbae381 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -110,6 +110,7 @@
#include "executor/nodeSort.h"
#include "executor/nodeSubplan.h"
#include "executor/nodeSubqueryscan.h"
+#include "executor/nodeTableFuncscan.h"
#include "executor/nodeTidscan.h"
#include "executor/nodeUnique.h"
#include "executor/nodeValuesscan.h"
@@ -239,6 +240,11 @@ ExecInitNode(Plan *node, EState *estate, int eflags)
estate, eflags);
break;
+ case T_TableFuncScan:
+ result = (PlanState *) ExecInitTableFuncScan((TableFuncScan *) node,
+ estate, eflags);
+ break;
+
case T_ValuesScan:
result = (PlanState *) ExecInitValuesScan((ValuesScan *) node,
estate, eflags);
@@ -459,6 +465,10 @@ ExecProcNode(PlanState *node)
result = ExecFunctionScan((FunctionScanState *) node);
break;
+ case T_TableFuncScanState:
+ result = ExecTableFuncScan((TableFuncScanState *) node);
+ break;
+
case T_ValuesScanState:
result = ExecValuesScan((ValuesScanState *) node);
break;
@@ -715,6 +725,10 @@ ExecEndNode(PlanState *node)
ExecEndFunctionScan((FunctionScanState *) node);
break;
+ case T_TableFuncScanState:
+ ExecEndTableFuncScan((TableFuncScanState *) node);
+ break;
+
case T_ValuesScanState:
ExecEndValuesScan((ValuesScanState *) node);
break;
diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c
new file mode 100644
index 0000000000..2d83a0e2bd
--- /dev/null
+++ b/src/backend/executor/nodeTableFuncscan.c
@@ -0,0 +1,540 @@
+/*-------------------------------------------------------------------------
+ *
+ * nodeTableFuncscan.c
+ * Support routines for scanning RangeTableFunc (XMLTABLE like functions).
+ *
+ * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/executor/nodeTableFuncscan.c
+ *
+ *-------------------------------------------------------------------------
+ */
+/*
+ * INTERFACE ROUTINES
+ * ExecTableFuncscan scans a function.
+ * ExecFunctionNext retrieve next tuple in sequential order.
+ * ExecInitTableFuncscan creates and initializes a TableFuncscan node.
+ * ExecEndTableFuncscan releases any storage allocated.
+ * ExecReScanTableFuncscan rescans the function
+ */
+#include "postgres.h"
+
+#include "catalog/pg_type.h"
+#include "executor/nodeTableFuncscan.h"
+#include "executor/tablefunc.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+#include "utils/tuplestore.h"
+#include "utils/xml.h"
+
+static TupleTableSlot *TableFuncNext(TableFuncScanState *node);
+static bool TableFuncRecheck(TableFuncScanState *node, TupleTableSlot *slot);
+
+static void FetchRows(TableFuncScanState *tstate, ExprContext *econtext);
+static void Initialize(TableFuncScanState *tstate, ExprContext *econtext, Datum doc);
+static bool FetchRow(TableFuncScanState *tstate, ExprContext *econtext);
+
+/* ----------------------------------------------------------------
+ * Scan Support
+ * ----------------------------------------------------------------
+ */
+/* ----------------------------------------------------------------
+ * TableFuncNext
+ *
+ * This is a workhorse for ExecTableFuncscan
+ * ----------------------------------------------------------------
+ */
+static TupleTableSlot *
+TableFuncNext(TableFuncScanState *node)
+{
+ TupleTableSlot *scanslot;
+
+ scanslot = node->ss.ss_ScanTupleSlot;
+
+ /*
+ * If first time through, read all tuples from function and put them
+ * in a tuplestore. Subsequent calls just fetch tuples from
+ * tuplestore.
+ */
+ if (node->tupstore == NULL)
+ FetchRows(node, node->ss.ps.ps_ExprContext);
+
+ /*
+ * Get the next tuple from tuplestore.
+ */
+ (void) tuplestore_gettupleslot(node->tupstore,
+ true,
+ false,
+ scanslot);
+ return scanslot;
+}
+
+/*
+ * TableFuncRecheck -- access method routine to recheck a tuple in EvalPlanQual
+ */
+static bool
+TableFuncRecheck(TableFuncScanState *node, TupleTableSlot *slot)
+{
+ /* nothing to check */
+ return true;
+}
+
+/* ----------------------------------------------------------------
+ * ExecTableFuncscan(node)
+ *
+ * Scans the function sequentially and returns the next qualifying
+ * tuple.
+ * We call the ExecScan() routine and pass it the appropriate
+ * access method functions.
+ * ----------------------------------------------------------------
+ */
+TupleTableSlot *
+ExecTableFuncScan(TableFuncScanState *node)
+{
+ return ExecScan(&node->ss,
+ (ExecScanAccessMtd) TableFuncNext,
+ (ExecScanRecheckMtd) TableFuncRecheck);
+}
+
+/* ----------------------------------------------------------------
+ * ExecInitTableFuncscan
+ * ----------------------------------------------------------------
+ */
+TableFuncScanState *
+ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags)
+{
+ TableFuncScanState *scanstate;
+ TableFunc *tf = node->tablefunc;
+ TupleDesc tupdesc;
+ int i;
+
+ /* check for unsupported flags */
+ Assert(!(eflags & EXEC_FLAG_MARK));
+
+ /*
+ * TableFuncscan should not have any children.
+ */
+ Assert(outerPlan(node) == NULL);
+ Assert(innerPlan(node) == NULL);
+
+ /*
+ * create new ScanState for node
+ */
+ scanstate = makeNode(TableFuncScanState);
+ scanstate->ss.ps.plan = (Plan *) node;
+ scanstate->ss.ps.state = estate;
+
+ /*
+ * Miscellaneous initialization
+ *
+ * create expression context for node
+ */
+ ExecAssignExprContext(estate, &scanstate->ss.ps);
+
+ /*
+ * initialize child expressions
+ */
+ scanstate->ss.ps.targetlist = (List *)
+ ExecInitExpr((Expr *) node->scan.plan.targetlist,
+ (PlanState *) scanstate);
+ scanstate->ss.ps.qual = (List *)
+ ExecInitExpr((Expr *) node->scan.plan.qual,
+ (PlanState *) scanstate);
+
+ /*
+ * tuple table initialization
+ */
+ ExecInitResultTupleSlot(estate, &scanstate->ss.ps);
+ ExecInitScanTupleSlot(estate, &scanstate->ss);
+
+ /*
+ * initialize source tuple type
+ */
+ tupdesc = BuildDescFromLists(tf->colnames,
+ tf->coltypes,
+ tf->coltypmods,
+ tf->colcollations);
+
+ ExecAssignScanType(&scanstate->ss, tupdesc);
+
+ /*
+ * Initialize result tuple type and projection info.
+ */
+ ExecAssignResultTypeFromTL(&scanstate->ss.ps);
+ ExecAssignScanProjectionInfo(&scanstate->ss);
+
+ scanstate->resultSlot = MakeSingleTupleTableSlot(tupdesc);
+
+ /* Only XmlTableBuilder is supported currently */
+ scanstate->routine = &XmlTableRoutine;
+
+ scanstate->buildercxt =
+ AllocSetContextCreate(CurrentMemoryContext,
+ "TableFunc builder context",
+ ALLOCSET_DEFAULT_SIZES);
+ scanstate->perValueCxt =
+ AllocSetContextCreate(scanstate->buildercxt,
+ "TableFunc per value context",
+ ALLOCSET_DEFAULT_SIZES);
+ scanstate->opaque = NULL; /* initialized at runtime */
+
+ scanstate->ns_names = tf->ns_names;
+
+ scanstate->ns_uris = (List *)
+ ExecInitExpr((Expr *) tf->ns_uris,
+ (PlanState *) scanstate);
+ scanstate->docexpr =
+ ExecInitExpr((Expr *) tf->docexpr,
+ (PlanState *) scanstate);
+ scanstate->rowexpr =
+ ExecInitExpr((Expr *) tf->rowexpr,
+ (PlanState *) scanstate);
+ scanstate->colexprs = (List *)
+ ExecInitExpr((Expr *) tf->colexprs,
+ (PlanState *) scanstate);
+ scanstate->coldefexprs = (List *)
+ ExecInitExpr((Expr *) tf->coldefexprs,
+ (PlanState *) scanstate);
+
+ /* these are allocated now and initialized later */
+ scanstate->notnulls = tf->notnulls;
+ scanstate->in_functions = palloc(sizeof(FmgrInfo) * tupdesc->natts);
+ scanstate->typioparams = palloc(sizeof(Oid) * tupdesc->natts);
+ scanstate->evalcols = tf->evalcols;
+ scanstate->ordinalitycol = tf->ordinalitycol;
+
+ /*
+ * Fill in the necessary fmgr infos.
+ */
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Oid in_funcid;
+
+ getTypeInputInfo(tupdesc->attrs[i]->atttypid,
+ &in_funcid, &scanstate->typioparams[i]);
+ fmgr_info(in_funcid, &scanstate->in_functions[i]);
+ }
+
+ return scanstate;
+}
+
+/* ----------------------------------------------------------------
+ * ExecEndTableFuncscan
+ *
+ * frees any storage allocated through C routines.
+ * ----------------------------------------------------------------
+ */
+void
+ExecEndTableFuncScan(TableFuncScanState *node)
+{
+ /*
+ * Free the exprcontext
+ */
+ ExecFreeExprContext(&node->ss.ps);
+
+ /*
+ * clean out the tuple table
+ */
+ ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
+ ExecClearTuple(node->ss.ss_ScanTupleSlot);
+
+ /*
+ * Release slots and tuplestore resources
+ */
+ ExecDropSingleTupleTableSlot(node->resultSlot);
+ if (node->tupstore != NULL)
+ tuplestore_end(node->tupstore);
+ node->tupstore = NULL;
+
+ MemoryContextDelete(node->buildercxt);
+}
+
+/* ----------------------------------------------------------------
+ * ExecReScanTableFuncscan
+ *
+ * Rescans the relation.
+ * ----------------------------------------------------------------
+ */
+void
+ExecReScanTableFuncScan(TableFuncScanState *node)
+{
+ Bitmapset *chgparam = node->ss.ps.chgParam;
+
+ ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
+ ExecClearTuple(node->resultSlot);
+
+ ExecScanReScan(&node->ss);
+
+ /*
+ * recompute when parameters are changed
+ */
+ if (chgparam)
+ {
+ if (node->tupstore != NULL)
+ {
+ tuplestore_end(node->tupstore);
+ node->tupstore = NULL;
+ }
+ }
+
+ if (node->tupstore != NULL)
+ tuplestore_rescan(node->tupstore);
+}
+
+/* ----------------------------------------------------------------
+ * FetchRows
+ *
+ * Reads a rows from TableFunc produces
+ *
+ * ----------------------------------------------------------------
+ */
+static void
+FetchRows(TableFuncScanState *tstate, ExprContext *econtext)
+{
+ const TableFuncRoutine *routine = tstate->routine;
+ MemoryContext oldcxt;
+ Datum value;
+ bool isnull;
+
+ Assert(tstate->opaque == NULL);
+
+ /* build tuplestore for result. */
+ oldcxt = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
+ tstate->tupstore = tuplestore_begin_heap(false, false, work_mem);
+
+ /* other work be done in builder context */
+ MemoryContextSwitchTo(tstate->buildercxt);
+
+ PG_TRY();
+ {
+ routine->InitBuilder(tstate);
+
+ /*
+ * If evaluating the document expression returns NULL, the table
+ * expression is empty and we return immediately.
+ */
+ value = ExecEvalExpr(tstate->docexpr, econtext, &isnull);
+
+ if (!isnull)
+ {
+ /* otherwise, pass the document value to the table builder */
+ Initialize(tstate, econtext, value);
+
+ /* skip all of the above on future executions of node */
+ tstate->rownum = 1;
+
+ while (FetchRow(tstate, econtext))
+ {
+ tuplestore_puttupleslot(tstate->tupstore, tstate->resultSlot);
+
+ /* reset short life context often */
+ MemoryContextReset(tstate->perValueCxt);
+ }
+
+ tuplestore_donestoring(tstate->tupstore);
+ tuplestore_rescan(tstate->tupstore);
+ }
+ }
+ PG_CATCH();
+ {
+ if (tstate->opaque != NULL)
+ routine->DestroyBuilder(tstate);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ /* Builder is not necessary now */
+ if (tstate->opaque != NULL)
+ {
+ routine->DestroyBuilder(tstate);
+ tstate->opaque = NULL;
+ }
+
+ /* return back memory context */
+ MemoryContextSwitchTo(oldcxt);
+
+ /* builder context is not necessary now */
+ MemoryContextResetOnly(tstate->buildercxt);
+
+ return;
+}
+
+/*
+ * Fill in namespace declarations, the row filter, and column filters in a
+ * table expression builder context.
+ */
+static void
+Initialize(TableFuncScanState *tstate, ExprContext *econtext, Datum doc)
+{
+ const TableFuncRoutine *routine;
+ TupleDesc tupdesc;
+ ListCell *lc1,
+ *lc2;
+ bool isnull;
+ int colno;
+ Datum value;
+
+ routine = tstate->routine;
+
+ /*
+ * The content can be bigger document and transformation to cstring can be
+ * expensive. The table builder is better place for this task - pass value
+ * as Datum. Evaluate builder function in special memory context
+ */
+ routine->SetDoc(tstate, doc);
+
+ /* Evaluate namespace specifications */
+ forboth(lc1, tstate->ns_uris, lc2, tstate->ns_names)
+ {
+ ExprState *expr = (ExprState *) lfirst(lc1);
+ char *ns_name = strVal(lfirst(lc2));
+ char *ns_uri;
+
+ value = ExecEvalExpr((ExprState *) expr, econtext, &isnull);
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("namespace URI must not be null")));
+ ns_uri = TextDatumGetCString(value);
+
+ routine->SetNamespace(tstate, ns_name, ns_uri);
+ }
+
+ /* Install the row filter expression into the table builder context */
+ value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull);
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("row filter expression must not be null")));
+
+ routine->SetRowFilter(tstate, TextDatumGetCString(value));
+
+ /*
+ * Install the column filter expressions into the table builder context.
+ * If an expression is given, use that; otherwise the column name itself
+ * is the column filter.
+ */
+ colno = 0;
+ tupdesc = tstate->resultSlot->tts_tupleDescriptor;
+ foreach(lc1, tstate->colexprs)
+ {
+ char *colfilter;
+
+ if (tstate->evalcols && colno != tstate->ordinalitycol)
+ {
+ ExprState *colexpr = lfirst(lc1);
+
+ if (colexpr != NULL)
+ {
+ value = ExecEvalExpr(colexpr, econtext, &isnull);
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("column filter expression must not be null"),
+ errdetail("Filter for column \"%s\" is null.",
+ NameStr(tupdesc->attrs[colno]->attname))));
+ colfilter = TextDatumGetCString(value);
+ }
+ else
+ colfilter = NameStr(tupdesc->attrs[colno]->attname);
+ }
+ else
+ colfilter = NULL;
+
+ routine->SetColumnFilter(tstate, colfilter, colno);
+
+ colno++;
+ }
+}
+
+/*
+ * Fetch one more row from a TableFunc table builder; if one can be obtained,
+ * push the values for each column onto the output.
+ */
+static bool
+FetchRow(TableFuncScanState *tstate, ExprContext *econtext)
+{
+ const TableFuncRoutine *routine = tstate->routine;
+ MemoryContext oldcxt;
+ bool gotrow;
+ TupleDesc tupdesc;
+ Datum *values;
+ bool *nulls;
+ ListCell *cell;
+ int natts;
+ int colno;
+ bool isnull;
+
+ /* Fetch a row */
+ gotrow = routine->FetchRow(tstate);
+
+ /* returns fast when there are not rows */
+ if (!gotrow)
+ return false;
+
+ tupdesc = tstate->resultSlot->tts_tupleDescriptor;
+ values = tstate->resultSlot->tts_values;
+ nulls = tstate->resultSlot->tts_isnull;
+ cell = list_head(tstate->coldefexprs);
+ natts = tupdesc->natts;
+ colno = 0;
+
+ ExecClearTuple(tstate->resultSlot);
+
+ /*
+ * Obtain the value of each column for this row, installing it into
+ * the values/isnull arrays.
+ */
+ for (colno = 0; colno < natts; colno++)
+ {
+ if (colno == tstate->ordinalitycol)
+ {
+ /* fast path when column is ordinality */
+ values[colno] = Int32GetDatum(tstate->rownum++);
+ nulls[colno] = false;
+ }
+ else
+ {
+ /* slow path: fetch value from builder */
+ oldcxt = MemoryContextSwitchTo(tstate->perValueCxt);
+ values[colno] = routine->GetValue(tstate,
+ tstate->evalcols ? colno : -1,
+ &isnull);
+ MemoryContextSwitchTo(oldcxt);
+
+ /* No value? Evaluate and apply the default, if any */
+ if (isnull && cell != NULL)
+ {
+ ExprState *coldefexpr = (ExprState *) lfirst(cell);
+
+ if (coldefexpr != NULL)
+ values[colno] = ExecEvalExpr(coldefexpr, econtext,
+ &isnull);
+ }
+
+ /* Verify a possible NOT NULL constraint */
+ if (isnull && bms_is_member(colno, tstate->notnulls))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null is not allowed in column \"%s\"",
+ NameStr(tupdesc->attrs[colno]->attname))));
+
+ nulls[colno] = isnull;
+ }
+
+ /* advance list of default expressions */
+ if (cell != NULL)
+ cell = lnext(cell);
+ }
+
+ ExecStoreVirtualTuple(tstate->resultSlot);
+
+ return true;
+}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 05d8538717..15b31edaa0 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -588,6 +588,27 @@ _copyFunctionScan(const FunctionScan *from)
}
/*
+ * _copyTableFuncScan
+ */
+static TableFuncScan *
+_copyTableFuncScan(const TableFuncScan *from)
+{
+ TableFuncScan *newnode = makeNode(TableFuncScan);
+
+ /*
+ * copy node superclass fields
+ */
+ CopyScanFields((const Scan *) from, (Scan *) newnode);
+
+ /*
+ * copy remainder of node
+ */
+ COPY_NODE_FIELD(tablefunc);
+
+ return newnode;
+}
+
+/*
* _copyValuesScan
*/
static ValuesScan *
@@ -1139,6 +1160,33 @@ _copyRangeVar(const RangeVar *from)
}
/*
+ * _copyTableFunc
+ */
+static TableFunc *
+_copyTableFunc(const TableFunc *from)
+{
+ TableFunc *newnode = makeNode(TableFunc);
+
+ COPY_NODE_FIELD(ns_names);
+ COPY_NODE_FIELD(ns_uris);
+ COPY_NODE_FIELD(docexpr);
+ COPY_NODE_FIELD(rowexpr);
+ COPY_SCALAR_FIELD(colcount);
+ COPY_NODE_FIELD(colnames);
+ COPY_NODE_FIELD(coltypes);
+ COPY_NODE_FIELD(coltypmods);
+ COPY_NODE_FIELD(colcollations);
+ COPY_NODE_FIELD(colexprs);
+ COPY_NODE_FIELD(coldefexprs);
+ COPY_BITMAPSET_FIELD(notnulls);
+ COPY_SCALAR_FIELD(ordinalitycol);
+ COPY_SCALAR_FIELD(evalcols);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
+/*
* _copyIntoClause
*/
static IntoClause *
@@ -2169,6 +2217,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_NODE_FIELD(joinaliasvars);
COPY_NODE_FIELD(functions);
COPY_SCALAR_FIELD(funcordinality);
+ COPY_NODE_FIELD(tablefunc);
COPY_NODE_FIELD(values_lists);
COPY_STRING_FIELD(ctename);
COPY_SCALAR_FIELD(ctelevelsup);
@@ -2590,6 +2639,38 @@ _copyRangeTableSample(const RangeTableSample *from)
return newnode;
}
+static RangeTableFunc *
+_copyRangeTableFunc(const RangeTableFunc *from)
+{
+ RangeTableFunc *newnode = makeNode(RangeTableFunc);
+
+ COPY_SCALAR_FIELD(lateral);
+ COPY_NODE_FIELD(docexpr);
+ COPY_NODE_FIELD(rowexpr);
+ COPY_NODE_FIELD(namespaces);
+ COPY_NODE_FIELD(columns);
+ COPY_NODE_FIELD(alias);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
+static RangeTableFuncCol *
+_copyRangeTableFuncCol(const RangeTableFuncCol *from)
+{
+ RangeTableFuncCol *newnode = makeNode(RangeTableFuncCol);
+
+ COPY_STRING_FIELD(colname);
+ COPY_NODE_FIELD(typeName);
+ COPY_SCALAR_FIELD(for_ordinality);
+ COPY_SCALAR_FIELD(is_not_null);
+ COPY_NODE_FIELD(colexpr);
+ COPY_NODE_FIELD(coldefexpr);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
static TypeCast *
_copyTypeCast(const TypeCast *from)
{
@@ -4550,6 +4631,9 @@ copyObject(const void *from)
case T_FunctionScan:
retval = _copyFunctionScan(from);
break;
+ case T_TableFuncScan:
+ retval = _copyTableFuncScan(from);
+ break;
case T_ValuesScan:
retval = _copyValuesScan(from);
break;
@@ -4626,6 +4710,9 @@ copyObject(const void *from)
case T_RangeVar:
retval = _copyRangeVar(from);
break;
+ case T_TableFunc:
+ retval = _copyTableFunc(from);
+ break;
case T_IntoClause:
retval = _copyIntoClause(from);
break;
@@ -5220,6 +5307,12 @@ copyObject(const void *from)
case T_RangeTableSample:
retval = _copyRangeTableSample(from);
break;
+ case T_RangeTableFunc:
+ retval = _copyRangeTableFunc(from);
+ break;
+ case T_RangeTableFuncCol:
+ retval = _copyRangeTableFuncCol(from);
+ break;
case T_TypeName:
retval = _copyTypeName(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index d595cd7481..c6b103198d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -117,6 +117,28 @@ _equalRangeVar(const RangeVar *a, const RangeVar *b)
}
static bool
+_equalTableFunc(const TableFunc *a, const TableFunc *b)
+{
+ COMPARE_NODE_FIELD(ns_names);
+ COMPARE_NODE_FIELD(ns_uris);
+ COMPARE_NODE_FIELD(docexpr);
+ COMPARE_NODE_FIELD(rowexpr);
+ COMPARE_SCALAR_FIELD(colcount);
+ COMPARE_NODE_FIELD(colnames);
+ COMPARE_NODE_FIELD(coltypes);
+ COMPARE_NODE_FIELD(coltypes);
+ COMPARE_NODE_FIELD(colcollations);
+ COMPARE_NODE_FIELD(colexprs);
+ COMPARE_NODE_FIELD(coldefexprs);
+ COMPARE_BITMAPSET_FIELD(notnulls);
+ COMPARE_SCALAR_FIELD(ordinalitycol);
+ COMPARE_SCALAR_FIELD(evalcols);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+}
+
+static bool
_equalIntoClause(const IntoClause *a, const IntoClause *b)
{
COMPARE_NODE_FIELD(rel);
@@ -2430,6 +2452,36 @@ _equalRangeTableSample(const RangeTableSample *a, const RangeTableSample *b)
}
static bool
+_equalRangeTableFunc(const RangeTableFunc *a, const RangeTableFunc *b)
+{
+ COMPARE_SCALAR_FIELD(lateral);
+ COMPARE_NODE_FIELD(docexpr);
+ COMPARE_NODE_FIELD(rowexpr);
+ COMPARE_NODE_FIELD(namespaces);
+ COMPARE_NODE_FIELD(columns);
+ COMPARE_NODE_FIELD(alias);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+}
+
+static bool
+_equalRangeTableFuncCol(const RangeTableFuncCol *a, const RangeTableFuncCol *b)
+{
+ COMPARE_STRING_FIELD(colname);
+ COMPARE_NODE_FIELD(typeName);
+ COMPARE_SCALAR_FIELD(for_ordinality);
+ COMPARE_NODE_FIELD(typeName);
+ COMPARE_SCALAR_FIELD(is_not_null);
+ COMPARE_NODE_FIELD(colexpr);
+ COMPARE_NODE_FIELD(coldefexpr);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+}
+
+
+static bool
_equalIndexElem(const IndexElem *a, const IndexElem *b)
{
COMPARE_STRING_FIELD(name);
@@ -2531,6 +2583,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_SCALAR_FIELD(jointype);
COMPARE_NODE_FIELD(joinaliasvars);
COMPARE_NODE_FIELD(functions);
+ COMPARE_NODE_FIELD(tablefunc);
COMPARE_SCALAR_FIELD(funcordinality);
COMPARE_NODE_FIELD(values_lists);
COMPARE_STRING_FIELD(ctename);
@@ -2897,6 +2950,9 @@ equal(const void *a, const void *b)
case T_RangeVar:
retval = _equalRangeVar(a, b);
break;
+ case T_TableFunc:
+ retval = _equalTableFunc(a, b);
+ break;
case T_IntoClause:
retval = _equalIntoClause(a, b);
break;
@@ -3478,6 +3534,12 @@ equal(const void *a, const void *b)
case T_RangeTableSample:
retval = _equalRangeTableSample(a, b);
break;
+ case T_RangeTableFunc:
+ retval = _equalRangeTableFunc(a, b);
+ break;
+ case T_RangeTableFuncCol:
+ retval = _equalRangeTableFuncCol(a, b);
+ break;
case T_TypeName:
retval = _equalTypeName(a, b);
break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c2f03b73f4..3deb1a28d1 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1217,6 +1217,9 @@ exprLocation(const Node *expr)
case T_RangeVar:
loc = ((const RangeVar *) expr)->location;
break;
+ case T_TableFunc:
+ loc = ((const TableFunc *) expr)->location;
+ break;
case T_Var:
loc = ((const Var *) expr)->location;
break;
@@ -2217,6 +2220,22 @@ expression_tree_walker(Node *node,
return true;
}
break;
+ case T_TableFunc:
+ {
+ TableFunc *tf = (TableFunc *) node;
+
+ if (walker(tf->ns_uris, context))
+ return true;
+ if (walker(tf->docexpr, context))
+ return true;
+ if (walker(tf->rowexpr, context))
+ return true;
+ if (walker(tf->colexprs, context))
+ return true;
+ if (walker(tf->coldefexprs, context))
+ return true;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
@@ -2324,6 +2343,10 @@ range_table_walker(List *rtable,
if (walker(rte->functions, context))
return true;
break;
+ case RTE_TABLEFUNC:
+ if (walker(rte->tablefunc, context))
+ return true;
+ break;
case RTE_VALUES:
if (walker(rte->values_lists, context))
return true;
@@ -3013,6 +3036,20 @@ expression_tree_mutator(Node *node,
return (Node *) newnode;
}
break;
+ case T_TableFunc:
+ {
+ TableFunc *tf = (TableFunc *) node;
+ TableFunc *newnode;
+
+ FLATCOPY(newnode, tf, TableFunc);
+ MUTATE(newnode->ns_uris, tf->ns_uris, List *);
+ MUTATE(newnode->docexpr, tf->docexpr, Node *);
+ MUTATE(newnode->rowexpr, tf->rowexpr, Node *);
+ MUTATE(newnode->colexprs, tf->colexprs, List *);
+ MUTATE(newnode->coldefexprs, tf->coldefexprs, List *);
+ return (Node *) newnode;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
@@ -3130,6 +3167,9 @@ range_table_mutator(List *rtable,
case RTE_FUNCTION:
MUTATE(newrte->functions, rte->functions, List *);
break;
+ case RTE_TABLEFUNC:
+ MUTATE(newrte->tablefunc, rte->tablefunc, TableFunc *);
+ break;
case RTE_VALUES:
MUTATE(newrte->values_lists, rte->values_lists, List *);
break;
@@ -3555,6 +3595,32 @@ raw_expression_tree_walker(Node *node,
return true;
}
break;
+ case T_RangeTableFunc:
+ {
+ RangeTableFunc *rtf = (RangeTableFunc *) node;
+
+ if (walker(rtf->docexpr, context))
+ return true;
+ if (walker(rtf->rowexpr, context))
+ return true;
+ if (walker(rtf->namespaces, context))
+ return true;
+ if (walker(rtf->columns, context))
+ return true;
+ if (walker(rtf->alias, context))
+ return true;
+ }
+ break;
+ case T_RangeTableFuncCol:
+ {
+ RangeTableFuncCol *rtfc = (RangeTableFuncCol *) node;
+
+ if (walker(rtfc->colexpr, context))
+ return true;
+ if (walker(rtfc->coldefexpr, context))
+ return true;
+ }
+ break;
case T_TypeName:
{
TypeName *tn = (TypeName *) node;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b3802b4428..09d7af6f52 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -566,6 +566,16 @@ _outFunctionScan(StringInfo str, const FunctionScan *node)
}
static void
+_outTableFuncScan(StringInfo str, const TableFuncScan *node)
+{
+ WRITE_NODE_TYPE("TABLEFUNCSCAN");
+
+ _outScanInfo(str, (const Scan *) node);
+
+ WRITE_NODE_FIELD(tablefunc);
+}
+
+static void
_outValuesScan(StringInfo str, const ValuesScan *node)
{
WRITE_NODE_TYPE("VALUESSCAN");
@@ -956,6 +966,28 @@ _outRangeVar(StringInfo str, const RangeVar *node)
}
static void
+_outTableFunc(StringInfo str, const TableFunc *node)
+{
+ WRITE_NODE_TYPE("TABLEFUNC");
+
+ WRITE_NODE_FIELD(ns_names);
+ WRITE_NODE_FIELD(ns_uris);
+ WRITE_NODE_FIELD(docexpr);
+ WRITE_NODE_FIELD(rowexpr);
+ WRITE_INT_FIELD(colcount);
+ WRITE_NODE_FIELD(colnames);
+ WRITE_NODE_FIELD(coltypes);
+ WRITE_NODE_FIELD(coltypmods);
+ WRITE_NODE_FIELD(colcollations);
+ WRITE_NODE_FIELD(colexprs);
+ WRITE_NODE_FIELD(coldefexprs);
+ WRITE_BITMAPSET_FIELD(notnulls);
+ WRITE_INT_FIELD(ordinalitycol);
+ WRITE_BOOL_FIELD(evalcols);
+ WRITE_LOCATION_FIELD(location);
+}
+
+static void
_outIntoClause(StringInfo str, const IntoClause *node)
{
WRITE_NODE_TYPE("INTOCLAUSE");
@@ -2869,6 +2901,9 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
WRITE_NODE_FIELD(functions);
WRITE_BOOL_FIELD(funcordinality);
break;
+ case RTE_TABLEFUNC:
+ WRITE_NODE_FIELD(tablefunc);
+ break;
case RTE_VALUES:
WRITE_NODE_FIELD(values_lists);
WRITE_NODE_FIELD(coltypes);
@@ -3192,6 +3227,34 @@ _outRangeTableSample(StringInfo str, const RangeTableSample *node)
}
static void
+_outRangeTableFunc(StringInfo str, const RangeTableFunc *node)
+{
+ WRITE_NODE_TYPE("RANGETABLEFUNC");
+
+ WRITE_BOOL_FIELD(lateral);
+ WRITE_NODE_FIELD(docexpr);
+ WRITE_NODE_FIELD(rowexpr);
+ WRITE_NODE_FIELD(namespaces);
+ WRITE_NODE_FIELD(columns);
+ WRITE_NODE_FIELD(alias);
+ WRITE_LOCATION_FIELD(location);
+}
+
+static void
+_outRangeTableFuncCol(StringInfo str, const RangeTableFuncCol *node)
+{
+ WRITE_NODE_TYPE("RANGETABLEFUNCCOL");
+
+ WRITE_STRING_FIELD(colname);
+ WRITE_NODE_FIELD(typeName);
+ WRITE_BOOL_FIELD(for_ordinality);
+ WRITE_BOOL_FIELD(is_not_null);
+ WRITE_NODE_FIELD(colexpr);
+ WRITE_NODE_FIELD(coldefexpr);
+ WRITE_LOCATION_FIELD(location);
+}
+
+static void
_outConstraint(StringInfo str, const Constraint *node)
{
WRITE_NODE_TYPE("CONSTRAINT");
@@ -3440,6 +3503,9 @@ outNode(StringInfo str, const void *obj)
case T_FunctionScan:
_outFunctionScan(str, obj);
break;
+ case T_TableFuncScan:
+ _outTableFuncScan(str, obj);
+ break;
case T_ValuesScan:
_outValuesScan(str, obj);
break;
@@ -3512,6 +3578,9 @@ outNode(StringInfo str, const void *obj)
case T_RangeVar:
_outRangeVar(str, obj);
break;
+ case T_TableFunc:
+ _outTableFunc(str, obj);
+ break;
case T_IntoClause:
_outIntoClause(str, obj);
break;
@@ -3922,6 +3991,12 @@ outNode(StringInfo str, const void *obj)
case T_RangeTableSample:
_outRangeTableSample(str, obj);
break;
+ case T_RangeTableFunc:
+ _outRangeTableFunc(str, obj);
+ break;
+ case T_RangeTableFuncCol:
+ _outRangeTableFuncCol(str, obj);
+ break;
case T_Constraint:
_outConstraint(str, obj);
break;
diff --git a/src/backend/nodes/print.c b/src/backend/nodes/print.c
index 926f226f34..dfb8bfa803 100644
--- a/src/backend/nodes/print.c
+++ b/src/backend/nodes/print.c
@@ -279,6 +279,10 @@ print_rt(const List *rtable)
printf("%d\t%s\t[rangefunction]",
i, rte->eref->aliasname);
break;
+ case RTE_TABLEFUNC:
+ printf("%d\t%s\t[table function]",
+ i, rte->eref->aliasname);
+ break;
case RTE_VALUES:
printf("%d\t%s\t[values list]",
i, rte->eref->aliasname);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index d2f69fe70b..f87d12945a 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -460,6 +460,33 @@ _readRangeVar(void)
READ_DONE();
}
+/*
+ * _readTableFunc
+ */
+static TableFunc *
+_readTableFunc(void)
+{
+ READ_LOCALS(TableFunc);
+
+ READ_NODE_FIELD(ns_names);
+ READ_NODE_FIELD(ns_uris);
+ READ_NODE_FIELD(docexpr);
+ READ_NODE_FIELD(rowexpr);
+ READ_INT_FIELD(colcount);
+ READ_NODE_FIELD(colnames);
+ READ_NODE_FIELD(coltypes);
+ READ_NODE_FIELD(coltypmods);
+ READ_NODE_FIELD(colcollations);
+ READ_NODE_FIELD(colexprs);
+ READ_NODE_FIELD(coldefexprs);
+ READ_BITMAPSET_FIELD(notnulls);
+ READ_INT_FIELD(ordinalitycol);
+ READ_BOOL_FIELD(evalcols);
+ READ_LOCATION_FIELD(location);
+
+ READ_DONE();
+}
+
static IntoClause *
_readIntoClause(void)
{
@@ -1315,6 +1342,9 @@ _readRangeTblEntry(void)
READ_NODE_FIELD(functions);
READ_BOOL_FIELD(funcordinality);
break;
+ case RTE_TABLEFUNC:
+ READ_NODE_FIELD(tablefunc);
+ break;
case RTE_VALUES:
READ_NODE_FIELD(values_lists);
READ_NODE_FIELD(coltypes);
@@ -1801,6 +1831,21 @@ _readValuesScan(void)
}
/*
+ * _readTableFuncScan
+ */
+static TableFuncScan *
+_readTableFuncScan(void)
+{
+ READ_LOCALS(TableFuncScan);
+
+ ReadCommonScan(&local_node->scan);
+
+ READ_NODE_FIELD(tablefunc);
+
+ READ_DONE();
+}
+
+/*
* _readCteScan
*/
static CteScan *
@@ -2358,6 +2403,8 @@ parseNodeString(void)
return_value = _readRangeVar();
else if (MATCH("INTOCLAUSE", 10))
return_value = _readIntoClause();
+ else if (MATCH("TABLEFUNC", 9))
+ return_value = _readTableFunc();
else if (MATCH("VAR", 3))
return_value = _readVar();
else if (MATCH("CONST", 5))
@@ -2500,6 +2547,8 @@ parseNodeString(void)
return_value = _readFunctionScan();
else if (MATCH("VALUESSCAN", 10))
return_value = _readValuesScan();
+ else if (MATCH("TABLEFUNCSCAN", 13))
+ return_value = _readTableFuncScan();
else if (MATCH("CTESCAN", 7))
return_value = _readCteScan();
else if (MATCH("WORKTABLESCAN", 13))
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index eeacf815e3..c7d4d6c160 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -106,6 +106,8 @@ static void set_function_pathlist(PlannerInfo *root, RelOptInfo *rel,
RangeTblEntry *rte);
static void set_values_pathlist(PlannerInfo *root, RelOptInfo *rel,
RangeTblEntry *rte);
+static void set_tablefunc_pathlist(PlannerInfo *root, RelOptInfo *rel,
+ RangeTblEntry *rte);
static void set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel,
RangeTblEntry *rte);
static void set_worktable_pathlist(PlannerInfo *root, RelOptInfo *rel,
@@ -365,6 +367,9 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
case RTE_FUNCTION:
set_function_size_estimates(root, rel);
break;
+ case RTE_TABLEFUNC:
+ set_tablefunc_size_estimates(root, rel);
+ break;
case RTE_VALUES:
set_values_size_estimates(root, rel);
break;
@@ -437,6 +442,10 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
/* RangeFunction */
set_function_pathlist(root, rel, rte);
break;
+ case RTE_TABLEFUNC:
+ /* Table Function */
+ set_tablefunc_pathlist(root, rel, rte);
+ break;
case RTE_VALUES:
/* Values list */
set_values_pathlist(root, rel, rte);
@@ -599,6 +608,12 @@ set_rel_consider_parallel(PlannerInfo *root, RelOptInfo *rel,
return;
break;
+ case RTE_TABLEFUNC:
+ /* Check for parallel-restricted functions. */
+ if (!is_parallel_safe(root, (Node *) rte->tablefunc))
+ return;
+ break;
+
case RTE_VALUES:
/* Check for parallel-restricted functions. */
if (!is_parallel_safe(root, (Node *) rte->values_lists))
@@ -1930,6 +1945,28 @@ set_values_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
}
/*
+ * set_tablefunc_pathlist
+ * Build the (single) access path for a table func RTE
+ */
+static void
+set_tablefunc_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
+{
+ Relids required_outer;
+ List *pathkeys = NIL;
+
+ /*
+ * We don't support pushing join clauses into the quals of a function
+ * scan, but it could still have required parameterization due to LATERAL
+ * refs in the function expression.
+ */
+ required_outer = rel->lateral_relids;
+
+ /* Generate appropriate path */
+ add_path(rel, create_tablefuncscan_path(root, rel,
+ pathkeys, required_outer));
+}
+
+/*
* set_cte_pathlist
* Build the (single) access path for a non-self-reference CTE RTE
*
@@ -3029,6 +3066,9 @@ print_path(PlannerInfo *root, Path *path, int indent)
case T_FunctionScan:
ptype = "FunctionScan";
break;
+ case T_TableFuncScan:
+ ptype = "TableFuncScan";
+ break;
case T_ValuesScan:
ptype = "ValuesScan";
break;
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index d01630f8db..665464c408 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -1279,6 +1279,62 @@ cost_functionscan(Path *path, PlannerInfo *root,
}
/*
+ * cost_tablefuncscan
+ * Determines and returns the cost of scanning a table function.
+ *
+ * 'baserel' is the relation to be scanned
+ * 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
+ */
+void
+cost_tablefuncscan(Path *path, PlannerInfo *root,
+ RelOptInfo *baserel, ParamPathInfo *param_info)
+{
+ Cost startup_cost = 0;
+ Cost run_cost = 0;
+ QualCost qpqual_cost;
+ Cost cpu_per_tuple;
+ RangeTblEntry *rte;
+ QualCost exprcost;
+
+ /* Should only be applied to base relations that are functions */
+ Assert(baserel->relid > 0);
+ rte = planner_rt_fetch(baserel->relid, root);
+ Assert(rte->rtekind == RTE_TABLEFUNC);
+
+ /* Mark the path with the correct row estimate */
+ if (param_info)
+ path->rows = param_info->ppi_rows;
+ else
+ path->rows = baserel->rows;
+
+ /*
+ * Estimate costs of executing the table func expression(s).
+ *
+ * XXX in principle we ought to charge tuplestore spill costs if the
+ * number of rows is large. However, given how phony our rowcount
+ * estimates for functions tend to be, there's not a lot of point in that
+ * refinement right now.
+ */
+ cost_qual_eval_node(&exprcost, (Node *) rte->tablefunc, root);
+
+ startup_cost += exprcost.startup + exprcost.per_tuple;
+
+ /* Add scanning CPU costs */
+ get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);
+
+ startup_cost += qpqual_cost.startup;
+ cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
+ run_cost += cpu_per_tuple * baserel->tuples;
+
+ /* tlist eval costs are paid per output row, not per tuple scanned */
+ startup_cost += path->pathtarget->cost.startup;
+ run_cost += path->pathtarget->cost.per_tuple * path->rows;
+
+ path->startup_cost = startup_cost;
+ path->total_cost = startup_cost + run_cost;
+}
+
+/*
* cost_valuesscan
* Determines and returns the cost of scanning a VALUES RTE.
*
@@ -4430,6 +4486,31 @@ set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel)
}
/*
+ * set_function_size_estimates
+ * Set the size estimates for a base relation that is a function call.
+ *
+ * The rel's targetlist and restrictinfo list must have been constructed
+ * already.
+ *
+ * We set the same fields as set_tablefunc_size_estimates.
+ */
+void
+set_tablefunc_size_estimates(PlannerInfo *root, RelOptInfo *rel)
+{
+ RangeTblEntry *rte;
+
+ /* Should only be applied to base relations that are functions */
+ Assert(rel->relid > 0);
+ rte = planner_rt_fetch(rel->relid, root);
+ Assert(rte->rtekind == RTE_TABLEFUNC);
+
+ rel->tuples = 100;
+
+ /* Now estimate number of output rows, etc */
+ set_baserel_size_estimates(root, rel);
+}
+
+/*
* set_values_size_estimates
* Set the size estimates for a base relation that is a values list.
*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 997bdcff2e..a363634ca6 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -134,6 +134,8 @@ static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path
List *tlist, List *scan_clauses);
static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
List *tlist, List *scan_clauses);
+static TableFuncScan *create_tablefuncscan_plan(PlannerInfo *root, Path *best_path,
+ List *tlist, List *scan_clauses);
static CteScan *create_ctescan_plan(PlannerInfo *root, Path *best_path,
List *tlist, List *scan_clauses);
static WorkTableScan *create_worktablescan_plan(PlannerInfo *root, Path *best_path,
@@ -190,6 +192,8 @@ static FunctionScan *make_functionscan(List *qptlist, List *qpqual,
Index scanrelid, List *functions, bool funcordinality);
static ValuesScan *make_valuesscan(List *qptlist, List *qpqual,
Index scanrelid, List *values_lists);
+static TableFuncScan *make_tablefuncscan(List *qptlist, List *qpqual,
+ Index scanrelid, TableFunc *tablefunc);
static CteScan *make_ctescan(List *qptlist, List *qpqual,
Index scanrelid, int ctePlanId, int cteParam);
static WorkTableScan *make_worktablescan(List *qptlist, List *qpqual,
@@ -355,6 +359,7 @@ create_plan_recurse(PlannerInfo *root, Path *best_path, int flags)
case T_TidScan:
case T_SubqueryScan:
case T_FunctionScan:
+ case T_TableFuncScan:
case T_ValuesScan:
case T_CteScan:
case T_WorkTableScan:
@@ -636,6 +641,13 @@ create_scan_plan(PlannerInfo *root, Path *best_path, int flags)
scan_clauses);
break;
+ case T_TableFuncScan:
+ plan = (Plan *) create_tablefuncscan_plan(root,
+ best_path,
+ tlist,
+ scan_clauses);
+ break;
+
case T_ValuesScan:
plan = (Plan *) create_valuesscan_plan(root,
best_path,
@@ -756,6 +768,7 @@ use_physical_tlist(PlannerInfo *root, Path *path, int flags)
rel->rtekind != RTE_SUBQUERY &&
rel->rtekind != RTE_FUNCTION &&
rel->rtekind != RTE_VALUES &&
+ rel->rtekind != RTE_TABLEFUNC &&
rel->rtekind != RTE_CTE)
return false;
@@ -3018,6 +3031,49 @@ create_functionscan_plan(PlannerInfo *root, Path *best_path,
}
/*
+ * create_tablefuncscan_plan
+ * Returns a tablefuncscan plan for the base relation scanned by 'best_path'
+ * with restriction clauses 'scan_clauses' and targetlist 'tlist'.
+ */
+static TableFuncScan *
+create_tablefuncscan_plan(PlannerInfo *root, Path *best_path,
+ List *tlist, List *scan_clauses)
+{
+ TableFuncScan *scan_plan;
+ Index scan_relid = best_path->parent->relid;
+ RangeTblEntry *rte;
+ TableFunc *tablefunc;
+
+ /* it should be a function base rel... */
+ Assert(scan_relid > 0);
+ rte = planner_rt_fetch(scan_relid, root);
+ Assert(rte->rtekind == RTE_TABLEFUNC);
+ tablefunc = rte->tablefunc;
+
+ /* Sort clauses into best execution order */
+ scan_clauses = order_qual_clauses(root, scan_clauses);
+
+ /* Reduce RestrictInfo list to bare expressions; ignore pseudoconstants */
+ scan_clauses = extract_actual_clauses(scan_clauses, false);
+
+ /* Replace any outer-relation variables with nestloop params */
+ if (best_path->param_info)
+ {
+ scan_clauses = (List *)
+ replace_nestloop_params(root, (Node *) scan_clauses);
+ /* The function expressions could contain nestloop params, too */
+ tablefunc = (TableFunc *) replace_nestloop_params(root, (Node *) tablefunc);
+ }
+
+ scan_plan = make_tablefuncscan(tlist, scan_clauses, scan_relid,
+ tablefunc);
+
+ copy_generic_path_info(&scan_plan->scan.plan, best_path);
+
+ return scan_plan;
+}
+
+/*
* create_valuesscan_plan
* Returns a valuesscan plan for the base relation scanned by 'best_path'
* with restriction clauses 'scan_clauses' and targetlist 'tlist'.
@@ -4915,6 +4971,25 @@ make_functionscan(List *qptlist,
return node;
}
+static TableFuncScan *
+make_tablefuncscan(List *qptlist,
+ List *qpqual,
+ Index scanrelid,
+ TableFunc *tablefunc)
+{
+ TableFuncScan *node = makeNode(TableFuncScan);
+ Plan *plan = &node->scan.plan;
+
+ plan->targetlist = qptlist;
+ plan->qual = qpqual;
+ plan->lefttree = NULL;
+ plan->righttree = NULL;
+ node->scan.scanrelid = scanrelid;
+ node->tablefunc = tablefunc;
+
+ return node;
+}
+
static ValuesScan *
make_valuesscan(List *qptlist,
List *qpqual,
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index c170e9614f..e736f86d14 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -337,6 +337,8 @@ extract_lateral_references(PlannerInfo *root, RelOptInfo *brel, Index rtindex)
vars = pull_vars_of_level((Node *) rte->functions, 0);
else if (rte->rtekind == RTE_VALUES)
vars = pull_vars_of_level((Node *) rte->values_lists, 0);
+ else if (rte->rtekind == RTE_TABLEFUNC)
+ vars = pull_vars_of_level((Node *) rte->tablefunc, 0);
else
{
Assert(false);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 3d33d46971..3988f9a7b4 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -69,17 +69,19 @@ create_upper_paths_hook_type create_upper_paths_hook = NULL;
/* Expression kind codes for preprocess_expression */
-#define EXPRKIND_QUAL 0
-#define EXPRKIND_TARGET 1
-#define EXPRKIND_RTFUNC 2
-#define EXPRKIND_RTFUNC_LATERAL 3
-#define EXPRKIND_VALUES 4
-#define EXPRKIND_VALUES_LATERAL 5
-#define EXPRKIND_LIMIT 6
-#define EXPRKIND_APPINFO 7
-#define EXPRKIND_PHV 8
-#define EXPRKIND_TABLESAMPLE 9
-#define EXPRKIND_ARBITER_ELEM 10
+#define EXPRKIND_QUAL 0
+#define EXPRKIND_TARGET 1
+#define EXPRKIND_RTFUNC 2
+#define EXPRKIND_RTFUNC_LATERAL 3
+#define EXPRKIND_VALUES 4
+#define EXPRKIND_VALUES_LATERAL 5
+#define EXPRKIND_LIMIT 6
+#define EXPRKIND_APPINFO 7
+#define EXPRKIND_PHV 8
+#define EXPRKIND_TABLESAMPLE 9
+#define EXPRKIND_ARBITER_ELEM 10
+#define EXPRKIND_TABLEFUNC 11
+#define EXPRKIND_TABLEFUNC_LATERAL 12
/* Passthrough data for standard_qp_callback */
typedef struct
@@ -685,7 +687,15 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
{
/* Preprocess the function expression(s) fully */
kind = rte->lateral ? EXPRKIND_RTFUNC_LATERAL : EXPRKIND_RTFUNC;
- rte->functions = (List *) preprocess_expression(root, (Node *) rte->functions, kind);
+ rte->functions = (List *)
+ preprocess_expression(root, (Node *) rte->functions, kind);
+ }
+ else if (rte->rtekind == RTE_TABLEFUNC)
+ {
+ /* Preprocess the function expression(s) fully */
+ kind = rte->lateral ? EXPRKIND_TABLEFUNC_LATERAL : EXPRKIND_TABLEFUNC;
+ rte->tablefunc = (TableFunc *)
+ preprocess_expression(root, (Node *) rte->tablefunc, kind);
}
else if (rte->rtekind == RTE_VALUES)
{
@@ -844,7 +854,8 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
if (root->hasJoinRTEs &&
!(kind == EXPRKIND_RTFUNC ||
kind == EXPRKIND_VALUES ||
- kind == EXPRKIND_TABLESAMPLE))
+ kind == EXPRKIND_TABLESAMPLE ||
+ kind == EXPRKIND_TABLEFUNC))
expr = flatten_join_alias_vars(root, expr);
/*
@@ -5167,6 +5178,8 @@ adjust_paths_for_srfs(PlannerInfo *root, RelOptInfo *rel,
PathTarget *thistarget = (PathTarget *) lfirst(lc1);
bool contains_srfs = (bool) lfirst_int(lc2);
+
+
/* If this level doesn't contain SRFs, do regular projection */
if (contains_srfs)
newpath = (Path *) create_set_projection_path(root,
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index be267b9da7..5dc1bb1fae 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -395,6 +395,7 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, RangeTblEntry *rte)
newrte->subquery = NULL;
newrte->joinaliasvars = NIL;
newrte->functions = NIL;
+ newrte->tablefunc = NULL;
newrte->values_lists = NIL;
newrte->coltypes = NIL;
newrte->coltypmods = NIL;
@@ -555,6 +556,19 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
fix_scan_list(root, splan->functions, rtoffset);
}
break;
+ case T_TableFuncScan:
+ {
+ TableFuncScan *splan = (TableFuncScan *) plan;
+
+ splan->scan.scanrelid += rtoffset;
+ splan->scan.plan.targetlist =
+ fix_scan_list(root, splan->scan.plan.targetlist, rtoffset);
+ splan->scan.plan.qual =
+ fix_scan_list(root, splan->scan.plan.qual, rtoffset);
+ splan->tablefunc = (TableFunc *)
+ fix_scan_expr(root, (Node *) splan->tablefunc, rtoffset);
+ }
+ break;
case T_ValuesScan:
{
ValuesScan *splan = (ValuesScan *) plan;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 7954c445dd..e8c7b431d0 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -2422,6 +2422,12 @@ finalize_plan(PlannerInfo *root, Plan *plan, Bitmapset *valid_params,
}
break;
+ case T_TableFuncScan:
+ finalize_primnode((Node *) ((TableFuncScan *) plan)->tablefunc,
+ &context);
+ context.paramids = bms_add_members(context.paramids, scan_params);
+ break;
+
case T_ValuesScan:
finalize_primnode((Node *) ((ValuesScan *) plan)->values_lists,
&context);
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 6911177b68..81074ec94a 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1118,6 +1118,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
case RTE_SUBQUERY:
case RTE_FUNCTION:
case RTE_VALUES:
+ case RTE_TABLEFUNC:
child_rte->lateral = true;
break;
case RTE_JOIN:
@@ -1965,6 +1966,11 @@ replace_vars_in_jointree(Node *jtnode,
pullup_replace_vars((Node *) rte->functions,
context);
break;
+ case RTE_TABLEFUNC:
+ rte->tablefunc = (TableFunc *)
+ pullup_replace_vars((Node *) rte->tablefunc,
+ context);
+ break;
case RTE_VALUES:
rte->values_lists = (List *)
pullup_replace_vars((Node *) rte->values_lists,
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 324829690d..90719aab11 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1751,6 +1751,32 @@ create_functionscan_path(PlannerInfo *root, RelOptInfo *rel,
}
/*
+ * create_tablefuncscan_path
+ * Creates a path corresponding to a sequential scan of a table function,
+ * returning the pathnode.
+ */
+Path *
+create_tablefuncscan_path(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys, Relids required_outer)
+{
+ Path *pathnode = makeNode(Path);
+
+ pathnode->pathtype = T_TableFuncScan;
+ pathnode->parent = rel;
+ pathnode->pathtarget = rel->reltarget;
+ pathnode->param_info = get_baserel_parampathinfo(root, rel,
+ required_outer);
+ pathnode->parallel_aware = false;
+ pathnode->parallel_safe = rel->consider_parallel;
+ pathnode->parallel_workers = 0;
+ pathnode->pathkeys = NIL; /* result is always unordered */
+
+ cost_tablefuncscan(pathnode, root, rel, pathnode->param_info);
+
+ return pathnode;
+}
+
+/*
* create_valuesscan_path
* Creates a path corresponding to a scan of a VALUES list,
* returning the pathnode.
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 4ed27054a1..7f65de4882 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1381,8 +1381,9 @@ relation_excluded_by_constraints(PlannerInfo *root,
* dropped cols.
*
* We also support building a "physical" tlist for subqueries, functions,
- * values lists, and CTEs, since the same optimization can occur in
- * SubqueryScan, FunctionScan, ValuesScan, CteScan, and WorkTableScan nodes.
+ * values lists, table exprssionsand CTEs, since the same optimization can
+ * occur in SubqueryScan, FunctionScan, ValuesScan, CteScan, TableFunc
+ * and WorkTableScan nodes.
*/
List *
build_physical_tlist(PlannerInfo *root, RelOptInfo *rel)
@@ -1454,6 +1455,7 @@ build_physical_tlist(PlannerInfo *root, RelOptInfo *rel)
break;
case RTE_FUNCTION:
+ case RTE_TABLEFUNC:
case RTE_VALUES:
case RTE_CTE:
/* Not all of these can have dropped cols, but share code anyway */
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index adc1db94f4..ebf9a80213 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -151,6 +151,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
case RTE_SUBQUERY:
case RTE_FUNCTION:
case RTE_VALUES:
+ case RTE_TABLEFUNC:
case RTE_CTE:
/*
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0f7659bb6b..2ce15b9c6d 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2776,6 +2776,15 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
LCS_asString(lc->strength)),
parser_errposition(pstate, thisrel->location)));
break;
+ case RTE_TABLEFUNC:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ /*------
+ translator: %s is a SQL row locking clause such as FOR UPDATE */
+ errmsg("%s cannot be applied to a table function",
+ LCS_asString(lc->strength)),
+ parser_errposition(pstate, thisrel->location)));
+ break;
case RTE_VALUES:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 07cc81ee76..943ff0b794 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -462,7 +462,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
%type <node> def_arg columnElem where_clause where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound
- columnref in_expr having_clause func_table array_expr
+ columnref in_expr having_clause func_table xmltable array_expr
ExclusionWhereClause
%type <list> rowsfrom_item rowsfrom_list opt_col_def_list
%type <boolean> opt_ordinality
@@ -549,6 +549,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> xmlexists_argument
%type <ival> document_or_content
%type <boolean> xml_whitespace_option
+%type <list> xmltable_column_list xmltable_column_opt_list
+%type <node> xmltable_column_el
+%type <defelt> xmltable_column_opt_el
+%type <list> xml_namespace_list
+%type <target> xml_namespace_el
%type <node> func_application func_expr_common_subexpr
%type <node> func_expr func_expr_windowless
@@ -606,10 +611,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CACHE CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
- CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
- COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT
- CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
- CROSS CSV CUBE CURRENT_P
+ CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS
+ COMMIT COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION
+ CONSTRAINT CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST
+ CREATE CROSS CSV CUBE CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
@@ -680,8 +685,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
- XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLPARSE
- XMLPI XMLROOT XMLSERIALIZE
+ XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
+ XMLPARSE XMLPI XMLROOT XMLSERIALIZE XMLTABLE
YEAR_P YES_P
@@ -11295,6 +11300,19 @@ table_ref: relation_expr opt_alias_clause
n->coldeflist = lsecond($3);
$$ = (Node *) n;
}
+ | xmltable opt_alias_clause
+ {
+ RangeTableFunc *n = (RangeTableFunc *) $1;
+ n->alias = $2;
+ $$ = (Node *) n;
+ }
+ | LATERAL_P xmltable opt_alias_clause
+ {
+ RangeTableFunc *n = (RangeTableFunc *) $2;
+ n->lateral = true;
+ n->alias = $3;
+ $$ = (Node *) n;
+ }
| select_with_parens opt_alias_clause
{
RangeSubselect *n = makeNode(RangeSubselect);
@@ -11734,6 +11752,181 @@ TableFuncElement: ColId Typename opt_collate_clause
}
;
+/*
+ * XMLTABLE
+ */
+xmltable:
+ XMLTABLE '(' c_expr xmlexists_argument ')'
+ {
+ RangeTableFunc *n = makeNode(RangeTableFunc);
+ n->rowexpr = $3;
+ n->docexpr = $4;
+ n->columns = NIL;
+ n->namespaces = NIL;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
+ | XMLTABLE '(' c_expr xmlexists_argument COLUMNS xmltable_column_list ')'
+ {
+ RangeTableFunc *n = makeNode(RangeTableFunc);
+ n->rowexpr = $3;
+ n->docexpr = $4;
+ n->columns = $6;
+ n->namespaces = NIL;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
+ | XMLTABLE '(' XMLNAMESPACES '(' xml_namespace_list ')' ','
+ c_expr xmlexists_argument ')'
+ {
+ RangeTableFunc *n = makeNode(RangeTableFunc);
+ n->rowexpr = $8;
+ n->docexpr = $9;
+ n->columns = NIL;
+ n->namespaces = $5;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
+ | XMLTABLE '(' XMLNAMESPACES '(' xml_namespace_list ')' ','
+ c_expr xmlexists_argument COLUMNS xmltable_column_list ')'
+ {
+ RangeTableFunc *n = makeNode(RangeTableFunc);
+ n->rowexpr = $8;
+ n->docexpr = $9;
+ n->columns = $11;
+ n->namespaces = $5;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
+ ;
+
+xmltable_column_list: xmltable_column_el { $$ = list_make1($1); }
+ | xmltable_column_list ',' xmltable_column_el { $$ = lappend($1, $3); }
+ ;
+
+xmltable_column_el:
+ ColId Typename
+ {
+ RangeTableFuncCol *fc = makeNode(RangeTableFuncCol);
+
+ fc->colname = $1;
+ fc->for_ordinality = false;
+ fc->typeName = $2;
+ fc->is_not_null = false;
+ fc->colexpr = NULL;
+ fc->coldefexpr = NULL;
+ fc->location = @1;
+
+ $$ = (Node *) fc;
+ }
+ | ColId Typename xmltable_column_opt_list
+ {
+ RangeTableFuncCol *fc = makeNode(RangeTableFuncCol);
+ ListCell *option;
+ bool nullability_seen = false;
+
+ fc->colname = $1;
+ fc->typeName = $2;
+ fc->for_ordinality = false;
+ fc->is_not_null = false;
+ fc->colexpr = NULL;
+ fc->coldefexpr = NULL;
+ fc->location = @1;
+
+ foreach(option, $3)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+
+ if (strcmp(defel->defname, "default") == 0)
+ {
+ if (fc->coldefexpr != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only one DEFAULT value is allowed"),
+ parser_errposition(defel->location)));
+ fc->coldefexpr = defel->arg;
+ }
+ else if (strcmp(defel->defname, "path") == 0)
+ {
+ if (fc->colexpr != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only one PATH value per column is allowed"),
+ parser_errposition(defel->location)));
+ fc->colexpr = defel->arg;
+ }
+ else if (strcmp(defel->defname, "is_not_null") == 0)
+ {
+ if (nullability_seen)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant NULL / NOT NULL declarations for column \"%s\"", fc->colname),
+ parser_errposition(defel->location)));
+ fc->is_not_null = intVal(defel->arg);
+ nullability_seen = true;
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized column option \"%s\"",
+ defel->defname),
+ parser_errposition(defel->location)));
+ }
+ }
+ $$ = (Node *) fc;
+ }
+ | ColId FOR ORDINALITY
+ {
+ RangeTableFuncCol *fc = makeNode(RangeTableFuncCol);
+
+ fc->colname = $1;
+ fc->for_ordinality = true;
+ /* other fields are ignored, initialized by makeNode */
+ fc->location = @1;
+
+ $$ = (Node *) fc;
+ }
+ ;
+
+xmltable_column_opt_list: xmltable_column_opt_el { $$ = list_make1($1); }
+ | xmltable_column_opt_list xmltable_column_opt_el { $$ = lappend($1, $2); }
+ ;
+
+xmltable_column_opt_el:
+ IDENT b_expr
+ { $$ = makeDefElem($1, $2, @1); }
+ | DEFAULT b_expr
+ { $$ = makeDefElem("default", $2, @1); }
+ | NOT NULL_P
+ { $$ = makeDefElem("is_not_null", (Node *) makeInteger(true), @1); }
+ | NULL_P
+ { $$ = makeDefElem("is_not_null", (Node *) makeInteger(false), @1); }
+ ;
+
+xml_namespace_list: xml_namespace_el { $$ = list_make1($1); }
+ | xml_namespace_list ',' xml_namespace_el { $$ = lappend($1, $3); }
+ ;
+
+xml_namespace_el:
+ b_expr AS ColLabel
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $3;
+ $$->indirection = NIL;
+ $$->val = $1;
+ $$->location = @1;
+ }
+ | DEFAULT b_expr
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = NULL;
+ $$->indirection = NIL;
+ $$->val = $2;
+ $$->location = @1;
+ }
+ ;
+
/*****************************************************************************
*
* Type syntax
@@ -14327,6 +14520,7 @@ unreserved_keyword:
| CLASS
| CLOSE
| CLUSTER
+ | COLUMNS
| COMMENT
| COMMENTS
| COMMIT
@@ -14632,10 +14826,12 @@ col_name_keyword:
| XMLELEMENT
| XMLEXISTS
| XMLFOREST
+ | XMLNAMESPACES
| XMLPARSE
| XMLPI
| XMLROOT
| XMLSERIALIZE
+ | XMLTABLE
;
/* Type/function identifier --- keywords that can be type or function names.
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 69f4736438..ef568c614d 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -22,6 +22,7 @@
#include "catalog/catalog.h"
#include "catalog/heap.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_constraint_fn.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
@@ -65,6 +66,8 @@ static RangeTblEntry *transformRangeSubselect(ParseState *pstate,
RangeSubselect *r);
static RangeTblEntry *transformRangeFunction(ParseState *pstate,
RangeFunction *r);
+static RangeTblEntry *transformRangeTableFunc(ParseState *pstate,
+ RangeTableFunc * t);
static TableSampleClause *transformRangeTableSample(ParseState *pstate,
RangeTableSample *rts);
static Node *transformFromClauseItem(ParseState *pstate, Node *n,
@@ -795,6 +798,250 @@ transformRangeTableSample(ParseState *pstate, RangeTableSample *rts)
return tablesample;
}
+/*
+ * Transform a table function - RangeTableFunc is raw form of TableFunc.
+ *
+ * Transform the namespace clauses, the document-generating expression, the
+ * row-generating expression, the column-generating expressions, and the
+ * default value expressions.
+ */
+static RangeTblEntry *
+transformRangeTableFunc(ParseState *pstate, RangeTableFunc * rtf)
+{
+ TableFunc *tf = makeNode(TableFunc);
+ const char *constructName;
+ Oid docType;
+ RangeTblEntry *rte;
+ bool is_lateral;
+
+ /* Currently only XMLTABLE is supported */
+ constructName = "XMLTABLE";
+ docType = XMLOID;
+
+ /*
+ * We make lateral_only names of this level visible, whether or not the
+ * RangeTableFunbc is explicitly marked LATERAL. This is needed for SQL
+ * spec compliance and seems useful on convenience grounds for all
+ * functions in FROM.
+ *
+ * (LATERAL can't nest within a single pstate level, so we don't need
+ * save/restore logic here.)
+ */
+ Assert(!pstate->p_lateral_active);
+ pstate->p_lateral_active = true;
+
+ /* Transform and apply typecast to the row-generating expression ... */
+ Assert(rtf->rowexpr != NULL);
+ tf->rowexpr = coerce_to_specific_type(pstate,
+ transformExpr(pstate, rtf->rowexpr, EXPR_KIND_FROM_FUNCTION),
+ TEXTOID,
+ constructName);
+ assign_expr_collations(pstate, tf->rowexpr);
+
+ /* ... and to the document itself */
+ Assert(rtf->docexpr != NULL);
+ tf->docexpr = coerce_to_specific_type(pstate,
+ transformExpr(pstate, rtf->docexpr, EXPR_KIND_FROM_FUNCTION),
+ docType,
+ constructName);
+ assign_expr_collations(pstate, tf->docexpr);
+
+ /* undef ordinality column number */
+ tf->ordinalitycol = -1;
+
+ /* Columns, if any, also need to be transformed */
+ if (rtf->columns != NIL)
+ {
+ ListCell *col;
+ char **names;
+ int i = 0;
+
+ tf->evalcols = true;
+
+ tf->colcount = list_length(rtf->columns);
+ names = palloc(sizeof(char *) * tf->colcount);
+
+ foreach(col, rtf->columns)
+ {
+ RangeTableFuncCol *rawc = (RangeTableFuncCol *) lfirst(col);
+ Oid typid;
+ int32 typmod;
+ Node *colexpr;
+ Node *coldefexpr;
+ int j;
+
+ tf->colnames = lappend(tf->colnames,
+ makeString(pstrdup(rawc->colname)));
+
+ /*
+ * Determine the type and typmod for the new column. FOR
+ * ORDINALITY columns are INTEGER per spec; the others are
+ * user-specified.
+ */
+ if (rawc->for_ordinality)
+ {
+ if (tf->ordinalitycol != -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only one FOR ORDINALITY column is allowed"),
+ parser_errposition(pstate, rawc->location)));
+
+ typid = INT4OID;
+ typmod = -1;
+ tf->ordinalitycol = i;
+ }
+ else
+ {
+ if (rawc->typeName->setof)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("column \"%s\" cannot be declared SETOF",
+ rawc->colname),
+ parser_errposition(pstate, rawc->location)));
+
+ typenameTypeIdAndMod(pstate, rawc->typeName,
+ &typid, &typmod);
+ }
+
+ tf->coltypes = lappend_oid(tf->coltypes, typid);
+ tf->coltypmods = lappend_int(tf->coltypmods, typmod);
+ tf->colcollations = lappend_oid(tf->colcollations,
+ type_is_collatable(typid) ? DEFAULT_COLLATION_OID : InvalidOid);
+
+ /* Transform the PATH and DEFAULT expressions */
+ if (rawc->colexpr)
+ {
+ colexpr = coerce_to_specific_type(pstate,
+ transformExpr(pstate, rawc->colexpr,
+ EXPR_KIND_FROM_FUNCTION),
+ TEXTOID,
+ constructName);
+ assign_expr_collations(pstate, colexpr);
+ }
+ else
+ colexpr = NULL;
+
+ if (rawc->coldefexpr)
+ {
+ coldefexpr = coerce_to_specific_type_typmod(pstate,
+ transformExpr(pstate, rawc->coldefexpr,
+ EXPR_KIND_FROM_FUNCTION),
+ typid, typmod,
+ constructName);
+ assign_expr_collations(pstate, coldefexpr);
+ }
+ else
+ coldefexpr = NULL;
+
+ tf->colexprs = lappend(tf->colexprs, colexpr);
+ tf->coldefexprs = lappend(tf->coldefexprs, coldefexpr);
+
+ if (rawc->is_not_null)
+ tf->notnulls = bms_add_member(tf->notnulls, i);
+
+ /* make sure column names are unique */
+ for (j = 0; j < i; j++)
+ if (strcmp(names[j], rawc->colname) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column name \"%s\" is not unique",
+ rawc->colname),
+ parser_errposition(pstate, rawc->location)));
+ names[i] = rawc->colname;
+
+ i++;
+ }
+ tf->evalcols = true;
+ pfree(names);
+ }
+ else
+ {
+ tf->evalcols = false;
+
+ /*
+ * When there are not explicit column, define the implicit one. The
+ * rules for implicit column can be different. Currently only XMLTABLE
+ * is supported.
+ */
+ tf->colcount = 1;
+ tf->colnames = list_make1(makeString(pstrdup("xmltable")));
+ tf->coltypes = list_make1_oid(XMLOID);
+ tf->coltypmods = list_make1_int(-1);
+ tf->colcollations = list_make1_oid(InvalidOid);
+ }
+
+ /* Namespaces, if any, also need to be transformed */
+ if (rtf->namespaces != NIL)
+ {
+ ListCell *ns;
+ ListCell *lc2;
+ List *ns_uris = NIL;
+ List *ns_names = NIL;
+ bool default_ns_seen = false;
+
+ foreach(ns, rtf->namespaces)
+ {
+ ResTarget *r = (ResTarget *) lfirst(ns);
+ Node *ns_uri;
+
+ Assert(IsA(r, ResTarget));
+ ns_uri = transformExpr(pstate, r->val, EXPR_KIND_FROM_FUNCTION);
+ ns_uri = coerce_to_specific_type(pstate, ns_uri,
+ TEXTOID, constructName);
+ assign_expr_collations(pstate, ns_uri);
+ ns_uris = lappend(ns_uris, ns_uri);
+
+ /* Verify consistency of name list: no dupes, only one DEFAULT */
+ if (r->name != NULL)
+ {
+ foreach(lc2, ns_names)
+ {
+ char *name = strVal(lfirst(lc2));
+
+ if (name == NULL)
+ continue;
+ if (strcmp(name, r->name) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("namespace name \"%s\" is not unique",
+ name),
+ parser_errposition(pstate, r->location)));
+ }
+ }
+ else
+ {
+ if (default_ns_seen)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only one default namespace is allowed"),
+ parser_errposition(pstate, r->location)));
+ default_ns_seen = true;
+ }
+
+ /* Note the string may be NULL */
+ ns_names = lappend(ns_names, makeString(r->name));
+ }
+
+ tf->ns_uris = ns_uris;
+ tf->ns_names = ns_names;
+ }
+
+ tf->location = rtf->location;
+
+ pstate->p_lateral_active = false;
+
+ /*
+ * Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
+ * there are any lateral cross-references in it.
+ */
+ is_lateral = rtf->lateral || contain_vars_of_level((Node *) tf, 0);
+
+ rte = addRangeTableEntryForTableFunc(pstate,
+ tf, rtf->alias, is_lateral, true);
+
+ return rte;
+}
+
/*
* transformFromClauseItem -
@@ -918,6 +1165,24 @@ transformFromClauseItem(ParseState *pstate, Node *n,
rte->tablesample = transformRangeTableSample(pstate, rts);
return (Node *) rtr;
}
+ else if (IsA(n, RangeTableFunc))
+ {
+ /* table function is like a plain relation */
+ RangeTblRef *rtr;
+ RangeTblEntry *rte;
+ int rtindex;
+
+ rte = transformRangeTableFunc(pstate, (RangeTableFunc *) n);
+ /* assume new rte is at end */
+ rtindex = list_length(pstate->p_rtable);
+ Assert(rte == rt_fetch(rtindex, pstate->p_rtable));
+ *top_rte = rte;
+ *top_rti = rtindex;
+ *namespace = list_make1(makeDefaultNSItem(rte));
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = rtindex;
+ return (Node *) rtr;
+ }
else if (IsA(n, JoinExpr))
{
/* A newfangled join expression */
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 2a2ac32157..2c3f3cd9ce 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -1096,9 +1096,9 @@ coerce_to_boolean(ParseState *pstate, Node *node,
}
/*
- * coerce_to_specific_type()
- * Coerce an argument of a construct that requires a specific data type.
- * Also check that input is not a set.
+ * coerce_to_specific_type_typmod()
+ * Coerce an argument of a construct that requires a specific data type,
+ * with a specific typmod. Also check that input is not a set.
*
* Returns the possibly-transformed node tree.
*
@@ -1106,9 +1106,9 @@ coerce_to_boolean(ParseState *pstate, Node *node,
* processing is wanted.
*/
Node *
-coerce_to_specific_type(ParseState *pstate, Node *node,
- Oid targetTypeId,
- const char *constructName)
+coerce_to_specific_type_typmod(ParseState *pstate, Node *node,
+ Oid targetTypeId, int32 targetTypmod,
+ const char *constructName)
{
Oid inputTypeId = exprType(node);
@@ -1117,7 +1117,7 @@ coerce_to_specific_type(ParseState *pstate, Node *node,
Node *newnode;
newnode = coerce_to_target_type(pstate, node, inputTypeId,
- targetTypeId, -1,
+ targetTypeId, targetTypmod,
COERCION_ASSIGNMENT,
COERCE_IMPLICIT_CAST,
-1);
@@ -1144,6 +1144,25 @@ coerce_to_specific_type(ParseState *pstate, Node *node,
return node;
}
+/*
+ * coerce_to_specific_type()
+ * Coerce an argument of a construct that requires a specific data type.
+ * Also check that input is not a set.
+ *
+ * Returns the possibly-transformed node tree.
+ *
+ * As with coerce_type, pstate may be NULL if no special unknown-Param
+ * processing is wanted.
+ */
+Node *
+coerce_to_specific_type(ParseState *pstate, Node *node,
+ Oid targetTypeId,
+ const char *constructName)
+{
+ return coerce_to_specific_type_typmod(pstate, node,
+ targetTypeId, -1,
+ constructName);
+}
/*
* parser_coercion_errposition - report coercion error location, if possible
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4b73272417..db100be336 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -15,6 +15,7 @@
#include "postgres.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
#include "miscadmin.h"
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index e693c316e3..e619074220 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1629,6 +1629,68 @@ addRangeTableEntryForFunction(ParseState *pstate,
}
/*
+ * Add an entry for a table function to the pstate's range table (p_rtable).
+ *
+ * This is much like addRangeTableEntry() except that it makes a values RTE.
+ */
+RangeTblEntry *
+addRangeTableEntryForTableFunc(ParseState *pstate,
+ TableFunc *tf,
+ Alias *alias,
+ bool lateral,
+ bool inFromCl)
+{
+ RangeTblEntry *rte = makeNode(RangeTblEntry);
+ char *refname = alias ? alias->aliasname : pstrdup("xmltable");
+ Alias *eref;
+ int numaliases;
+
+ Assert(pstate != NULL);
+
+ rte->rtekind = RTE_TABLEFUNC;
+ rte->relid = InvalidOid;
+ rte->subquery = NULL;
+ rte->tablefunc = tf;
+ rte->coltypes = tf->coltypes;
+ rte->coltypmods = tf->coltypmods;
+ rte->colcollations = tf->colcollations;
+ rte->alias = alias;
+
+ eref = alias ? copyObject(alias) : makeAlias(refname, NIL);
+ numaliases = list_length(eref->colnames);
+
+ /* fill in any unspecified alias columns */
+ if (numaliases < list_length(tf->colnames))
+ eref->colnames = list_concat(eref->colnames,
+ list_copy_tail(tf->colnames, numaliases));
+
+ rte->eref = eref;
+
+ /*
+ * Set flags and access permissions.
+ *
+ * Subqueries are never checked for access rights.
+ */
+ rte->lateral = lateral;
+ rte->inh = false; /* never true for values RTEs */
+ rte->inFromCl = inFromCl;
+
+ rte->requiredPerms = 0;
+ rte->checkAsUser = InvalidOid;
+ rte->selectedCols = NULL;
+ rte->insertedCols = NULL;
+ rte->updatedCols = NULL;
+
+ /*
+ * Add completed RTE to pstate's range table list, but not to join list
+ * nor namespace --- caller must do that if appropriate.
+ */
+ pstate->p_rtable = lappend(pstate->p_rtable, rte);
+
+ return rte;
+}
+
+/*
* Add an entry for a VALUES list to the pstate's range table (p_rtable).
*
* This is much like addRangeTableEntry() except that it makes a values RTE.
@@ -2228,6 +2290,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
}
break;
case RTE_VALUES:
+ case RTE_TABLEFUNC:
case RTE_CTE:
{
/* Values or CTE RTE */
@@ -2639,6 +2702,7 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum,
*varcollid = exprCollation(aliasvar);
}
break;
+ case RTE_TABLEFUNC:
case RTE_VALUES:
case RTE_CTE:
{
@@ -2685,9 +2749,13 @@ get_rte_attribute_is_dropped(RangeTblEntry *rte, AttrNumber attnum)
}
break;
case RTE_SUBQUERY:
+ case RTE_TABLEFUNC:
case RTE_VALUES:
case RTE_CTE:
- /* Subselect, Values, CTE RTEs never have dropped columns */
+ /*
+ * Subselect, Table Functions, Values, CTE RTEs never have dropped
+ * columns
+ */
result = false;
break;
case RTE_JOIN:
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 2576e31239..3b84140a9b 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -396,6 +396,7 @@ markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
break;
case RTE_FUNCTION:
case RTE_VALUES:
+ case RTE_TABLEFUNC:
/* not a simple relation, leave it unmarked */
break;
case RTE_CTE:
@@ -1557,6 +1558,12 @@ expandRecordVariable(ParseState *pstate, Var *var, int levelsup)
* its result columns as RECORD, which is not allowed.
*/
break;
+ case RTE_TABLEFUNC:
+
+ /*
+ * Table function cannot have columns with RECORD type.
+ */
+ break;
case RTE_CTE:
/* CTE reference: examine subquery's output expr */
if (!rte->self_reference)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index d3e44fb135..d069e8a547 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -433,6 +433,10 @@ rewriteRuleAction(Query *parsetree,
sub_action->hasSubLinks =
checkExprHasSubLink((Node *) rte->functions);
break;
+ case RTE_TABLEFUNC:
+ sub_action->hasSubLinks =
+ checkExprHasSubLink((Node *) rte->tablefunc);
+ break;
case RTE_VALUES:
sub_action->hasSubLinks =
checkExprHasSubLink((Node *) rte->values_lists);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index f355954b53..9ac3df76e1 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -428,6 +428,8 @@ static void get_const_expr(Const *constval, deparse_context *context,
static void get_const_collation(Const *constval, deparse_context *context);
static void simple_quote_literal(StringInfo buf, const char *val);
static void get_sublink_expr(SubLink *sublink, deparse_context *context);
+static void get_tablefunc(TableFunc *tf, deparse_context *context,
+ bool showimplicit);
static void get_from_clause(Query *query, const char *prefix,
deparse_context *context);
static void get_from_clause_item(Node *jtnode, Query *query,
@@ -6709,6 +6711,7 @@ get_name_for_var_field(Var *var, int fieldno,
/* else fall through to inspect the expression */
break;
case RTE_FUNCTION:
+ case RTE_TABLEFUNC:
/*
* We couldn't get here unless a function is declared with one of
@@ -8548,6 +8551,10 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_TableFunc:
+ get_tablefunc((TableFunc *) node, context, showimplicit);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
break;
@@ -9286,6 +9293,124 @@ get_sublink_expr(SubLink *sublink, deparse_context *context)
/* ----------
+ * get_tablefunc - Parse back a table function
+ * ----------
+ */
+static void
+get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit)
+{
+ StringInfo buf = context->buf;
+
+ /*
+ * Deparse TableFunc - now only one TableFunc producer, the
+ * function XMLTABLE.
+ */
+
+ /* c_expr shoud be closed in brackets */
+ appendStringInfoString(buf, "XMLTABLE(");
+
+ if (tf->ns_uris != NIL)
+ {
+ ListCell *lc1,
+ *lc2;
+ bool first = true;
+
+ appendStringInfoString(buf, "XMLNAMESPACES (");
+ forboth(lc1, tf->ns_uris, lc2, tf->ns_names)
+ {
+ Node *expr = (Node *) lfirst(lc1);
+ char *name = strVal(lfirst(lc2));
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ else
+ first = false;
+
+ if (name != NULL)
+ {
+ get_rule_expr(expr, context, showimplicit);
+ appendStringInfo(buf, " AS %s", name);
+ }
+ else
+ {
+ appendStringInfoString(buf, "DEFAULT ");
+ get_rule_expr(expr, context, showimplicit);
+ }
+ }
+ appendStringInfoString(buf, "), ");
+ }
+
+ appendStringInfoChar(buf, '(');
+ get_rule_expr((Node *) tf->rowexpr, context, showimplicit);
+ appendStringInfoString(buf, ") PASSING (");
+ get_rule_expr((Node *) tf->docexpr, context, showimplicit);
+ appendStringInfoChar(buf, ')');
+
+ if (tf->evalcols)
+ {
+ ListCell *l1;
+ ListCell *l2;
+ ListCell *l3;
+ ListCell *l4;
+ ListCell *l5;
+ int colnum = 0;
+
+ l2 = list_head(tf->coltypes);
+ l3 = list_head(tf->coltypmods);
+ l4 = list_head(tf->colexprs);
+ l5 = list_head(tf->coldefexprs);
+
+ appendStringInfoString(buf, " COLUMNS ");
+ foreach(l1, tf->colnames)
+ {
+ char *colname = strVal(lfirst(l1));
+ Oid typid;
+ int32 typmod;
+ Node *colexpr;
+ Node *coldefexpr;
+ bool ordinality = tf->ordinalitycol == colnum;
+ bool notnull = bms_is_member(colnum, tf->notnulls);
+
+ typid = lfirst_oid(l2);
+ l2 = lnext(l2);
+ typmod = lfirst_int(l3);
+ l3 = lnext(l3);
+ colexpr = (Node *) lfirst(l4);
+ l4 = lnext(l4);
+ coldefexpr = (Node *) lfirst(l5);
+ l5 = lnext(l5);
+
+ if (colnum > 0)
+ appendStringInfoString(buf, ", ");
+ colnum++;
+
+ appendStringInfo(buf, "%s %s", quote_identifier(colname),
+ ordinality ? "FOR ORDINALITY" :
+ format_type_with_typemod(typid, typmod));
+ if (ordinality)
+ continue;
+
+ if (coldefexpr != NULL)
+ {
+ appendStringInfoString(buf, " DEFAULT (");
+ get_rule_expr((Node *) coldefexpr, context, showimplicit);
+ appendStringInfoChar(buf, ')');
+ }
+ if (colexpr != NULL)
+ {
+ appendStringInfoString(buf, " PATH (");
+ get_rule_expr((Node *) colexpr, context, showimplicit);
+ appendStringInfoChar(buf, ')');
+ }
+ if (notnull)
+ appendStringInfoString(buf, " NOT NULL");
+ }
+ }
+
+ appendStringInfoChar(buf, ')');
+}
+
+/* ----------
* get_from_clause - Parse back a FROM clause
*
* "prefix" is the keyword that denotes the start of the list of FROM
@@ -9518,6 +9643,9 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
if (rte->funcordinality)
appendStringInfoString(buf, " WITH ORDINALITY");
break;
+ case RTE_TABLEFUNC:
+ get_tablefunc(rte->tablefunc, context, true);
+ break;
case RTE_VALUES:
/* Values list RTE */
appendStringInfoChar(buf, '(');
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index e8bce3b806..85785f4411 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -73,6 +73,7 @@
#include "commands/dbcommands.h"
#include "executor/executor.h"
#include "executor/spi.h"
+#include "executor/tablefunc.h"
#include "fmgr.h"
#include "lib/stringinfo.h"
#include "libpq/pqformat.h"
@@ -145,6 +146,7 @@ static text *xml_xmlnodetoxmltype(xmlNodePtr cur, PgXmlErrorContext *xmlerrcxt);
static int xml_xpathobjtoxmlarray(xmlXPathObjectPtr xpathobj,
ArrayBuildState *astate,
PgXmlErrorContext *xmlerrcxt);
+static xmlChar *pg_xmlCharStrndup(char *str, size_t len);
#endif /* USE_LIBXML */
static StringInfo query_to_xml_internal(const char *query, char *tablename,
@@ -165,6 +167,49 @@ static void SPI_sql_row_to_xmlelement(uint64 rownum, StringInfo result,
char *tablename, bool nulls, bool tableforest,
const char *targetns, bool top_level);
+/* XMLTABLE support */
+#ifdef USE_LIBXML
+/* random number to identify XmlTableContext */
+#define XMLTABLE_CONTEXT_MAGIC 46922182
+typedef struct XmlTableBuilderData
+{
+ int magic;
+ char *def_namespace_name;
+ long int row_count;
+ PgXmlErrorContext *xmlerrcxt;
+ xmlParserCtxtPtr ctxt;
+ xmlDocPtr doc;
+ xmlXPathContextPtr xpathcxt;
+ xmlXPathCompExprPtr xpathcomp;
+ xmlXPathObjectPtr xpathobj;
+ xmlXPathCompExprPtr *xpathscomp;
+} XmlTableBuilderData;
+#endif
+
+static void XmlTableInitBuilder(TableFuncScanState *state);
+static void XmlTableSetDoc(TableFuncScanState *state, Datum value);
+static void XmlTableSetNamespace(TableFuncScanState *state, char *name,
+ char *uri);
+static void XmlTableSetRowFilter(TableFuncScanState *state, char *path);
+static void XmlTableSetColumnFilter(TableFuncScanState *state, char *path,
+ int colnum);
+static bool XmlTableFetchRow(TableFuncScanState *state);
+static Datum XmlTableGetValue(TableFuncScanState *state, int colnum,
+ bool *isnull);
+static void XmlTableDestroyBuilder(TableFuncScanState *state);
+
+const TableFuncRoutine XmlTableRoutine =
+{
+ XmlTableInitBuilder,
+ XmlTableSetDoc,
+ XmlTableSetNamespace,
+ XmlTableSetRowFilter,
+ XmlTableSetColumnFilter,
+ XmlTableFetchRow,
+ XmlTableGetValue,
+ XmlTableDestroyBuilder
+};
+
#define NO_XML_SUPPORT() \
ereport(ERROR, \
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), \
@@ -1113,6 +1158,19 @@ xml_pnstrdup(const xmlChar *str, size_t len)
return result;
}
+/* Ditto, except input is char* */
+static xmlChar *
+pg_xmlCharStrndup(char *str, size_t len)
+{
+ xmlChar *result;
+
+ result = (xmlChar *) palloc((len + 1) * sizeof(xmlChar));
+ memcpy(result, str, len);
+ result[len] = '\0';
+
+ return result;
+}
+
/*
* str is the null-terminated input string. Remaining arguments are
* output arguments; each can be NULL if value is not wanted.
@@ -3811,13 +3869,8 @@ xpath_internal(text *xpath_expr_text, xmltype *data, ArrayType *namespaces,
(errcode(ERRCODE_DATA_EXCEPTION),
errmsg("empty XPath expression")));
- string = (xmlChar *) palloc((len + 1) * sizeof(xmlChar));
- memcpy(string, datastr, len);
- string[len] = '\0';
-
- xpath_expr = (xmlChar *) palloc((xpath_len + 1) * sizeof(xmlChar));
- memcpy(xpath_expr, VARDATA(xpath_expr_text), xpath_len);
- xpath_expr[xpath_len] = '\0';
+ string = pg_xmlCharStrndup(datastr, len);
+ xpath_expr = pg_xmlCharStrndup(VARDATA(xpath_expr_text), xpath_len);
xmlerrcxt = pg_xml_init(PG_XML_STRICTNESS_ALL);
@@ -4065,3 +4118,502 @@ xml_is_well_formed_content(PG_FUNCTION_ARGS)
return 0;
#endif /* not USE_LIBXML */
}
+
+/*
+ * support functions for XMLTABLE
+ *
+ */
+#ifdef USE_LIBXML
+
+/*
+ * Returns private data from executor state. Ensure validity by check with
+ * MAGIC number.
+ */
+static inline XmlTableBuilderData *
+GetXmlTableBuilderPrivateData(TableFuncScanState *state, const char *fname)
+{
+ XmlTableBuilderData *result;
+
+ if (!IsA(state, TableFuncScanState))
+ elog(ERROR, "%s called with invalid TableFuncScanState", fname);
+ result = (XmlTableBuilderData *) state->opaque;
+ if (result->magic != XMLTABLE_CONTEXT_MAGIC)
+ elog(ERROR, "%s called with invalid TableFuncScanState", fname);
+
+ return result;
+}
+#endif
+
+/*
+ * XmlTableInitBuilder
+ * Fill in TableFuncScanState for XmlTable builder. Initialize XML parser.
+ */
+static void
+XmlTableInitBuilder(TableFuncScanState *state)
+{
+#ifdef USE_LIBXML
+ volatile xmlParserCtxtPtr ctxt = NULL;
+ XmlTableBuilderData *xtCxt;
+ PgXmlErrorContext *xmlerrcxt;
+
+ xtCxt = palloc0(sizeof(XmlTableBuilderData));
+ xtCxt->magic = XMLTABLE_CONTEXT_MAGIC;
+ xtCxt->xpathscomp = palloc0(sizeof(xmlXPathCompExprPtr) *
+ state->resultSlot->tts_tupleDescriptor->natts);
+
+ xmlerrcxt = pg_xml_init(PG_XML_STRICTNESS_ALL);
+
+ PG_TRY();
+ {
+ xmlInitParser();
+
+ ctxt = xmlNewParserCtxt();
+ if (ctxt == NULL || xmlerrcxt->err_occurred)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_OUT_OF_MEMORY,
+ "could not allocate parser context");
+ }
+ PG_CATCH();
+ {
+ if (ctxt != NULL)
+ xmlFreeParserCtxt(ctxt);
+
+ pg_xml_done(xmlerrcxt, true);
+
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ xtCxt->xmlerrcxt = xmlerrcxt;
+ xtCxt->ctxt = ctxt;
+
+ state->opaque = xtCxt;
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
+
+/*
+ * XmlTableSetDoc
+ * Install the input document
+ */
+static void
+XmlTableSetDoc(TableFuncScanState *state, Datum value)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+ xmltype *xmlval = DatumGetXmlP(value);
+ xmlChar *xstr;
+ int length;
+ volatile xmlDocPtr doc = NULL;
+ volatile xmlXPathContextPtr xpathcxt = NULL;
+
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableSetDoc");
+
+ length = VARSIZE(xmlval) - VARHDRSZ;
+ xstr = pg_xmlCharStrndup(VARDATA(xmlval), length);
+
+ PG_TRY();
+ {
+ doc = xmlCtxtReadMemory(xtCxt->ctxt, (char *) xstr, length, NULL, NULL, 0);
+ if (doc == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT,
+ "could not parse XML document");
+ xpathcxt = xmlXPathNewContext(doc);
+ if (xpathcxt == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_OUT_OF_MEMORY,
+ "could not allocate XPath context");
+ xpathcxt->node = xmlDocGetRootElement(doc);
+ if (xpathcxt->node == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
+ "could not find root XML element");
+ }
+ PG_CATCH();
+ {
+ if (xpathcxt != NULL)
+ xmlXPathFreeContext(xpathcxt);
+ if (doc != NULL)
+ xmlFreeDoc(doc);
+
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ xtCxt->doc = doc;
+ xtCxt->xpathcxt = xpathcxt;
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
+
+/*
+ * XmlTableSetNamespace
+ * Add a namespace declaration
+ */
+static void
+XmlTableSetNamespace(TableFuncScanState *state, char *name, char *uri)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+
+ if (name == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DEFAULT namespace is not supported")));
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableSetNamespace");
+
+ if (xmlXPathRegisterNs(xtCxt->xpathcxt,
+ pg_xmlCharStrndup(name, strlen(name)),
+ pg_xmlCharStrndup(uri, strlen(uri))))
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_DATA_EXCEPTION,
+ "could not set XML namespace");
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
+
+/*
+ * XmlTableSetRowFilter
+ * Install the row-filter Xpath expression.
+ */
+static void
+XmlTableSetRowFilter(TableFuncScanState *state, char *path)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+ xmlChar *xstr;
+
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableSetRowFilter");
+
+ if (*path == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_EXCEPTION),
+ errmsg("row path filter must not be empty string")));
+
+ xstr = pg_xmlCharStrndup(path, strlen(path));
+
+ xtCxt->xpathcomp = xmlXPathCompile(xstr);
+ if (xtCxt->xpathcomp == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_DATA_EXCEPTION,
+ "invalid XPath expression");
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
+
+/*
+ * XmlTableSetColumnFilter
+ * Install the column-filter Xpath expression, for the given column.
+ *
+ * The path can be NULL, when the only one result column is implicit. XXX fix
+ */
+static void
+XmlTableSetColumnFilter(TableFuncScanState *state, char *path, int colnum)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+ xmlChar *xstr;
+
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableSetColumnFilter");
+
+ if (path != NULL)
+ {
+ if (*path == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_EXCEPTION),
+ errmsg("column path filter must not be empty string")));
+
+ xstr = pg_xmlCharStrndup(path, strlen(path));
+
+ xtCxt->xpathscomp[colnum] = xmlXPathCompile(xstr);
+ if (xtCxt->xpathscomp[colnum] == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_DATA_EXCEPTION,
+ "invalid XPath expression");
+ }
+ else
+ {
+ Assert(!state->evalcols || colnum == state->ordinalitycol);
+
+ xtCxt->xpathscomp[colnum] = NULL;
+ }
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
+
+/*
+ * XmlTableFetchRow
+ * Prepare the next "current" tuple for upcoming GetValue calls.
+ * Returns FALSE if the row-filter expression returned no more rows.
+ */
+static bool
+XmlTableFetchRow(TableFuncScanState *state)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableFetchRow");
+
+ /*
+ * XmlTable returns table - set of composite values. The error context, is
+ * used for producement more values, between two calls, there can be
+ * created and used another libxml2 error context. It is libxml2 global
+ * value, so it should be refreshed any time before any libxml2 usage,
+ * that is finished by returning some value.
+ */
+ xmlSetStructuredErrorFunc((void *) xtCxt->xmlerrcxt, xml_errorHandler);
+
+ if (xtCxt->xpathobj == NULL)
+ {
+ xtCxt->xpathobj = xmlXPathCompiledEval(xtCxt->xpathcomp, xtCxt->xpathcxt);
+ if (xtCxt->xpathobj == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
+ "could not create XPath object");
+
+ xtCxt->row_count = 0;
+ }
+
+ if (xtCxt->xpathobj->type == XPATH_NODESET)
+ {
+ if (xtCxt->xpathobj->nodesetval != NULL)
+ {
+ if (xtCxt->row_count++ < xtCxt->xpathobj->nodesetval->nodeNr)
+ return true;
+ }
+ }
+
+ return false;
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+
+ return false;
+}
+
+/*
+ * XmlTableGetValue
+ * Return the value for column number 'colnum' for the current row. If
+ * column -1 is requested, return representation of the whole row.
+ *
+ * This leaks memory, so be sure to reset often the context in which it's
+ * called.
+ */
+static Datum
+XmlTableGetValue(TableFuncScanState *state, int colnum, bool *isnull)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+ Datum result = (Datum) 0;
+ xmlNodePtr cur;
+ char *cstr = NULL;
+ volatile xmlXPathObjectPtr xpathobj = NULL;
+
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableGetValue");
+
+ Assert(xtCxt->xpathobj &&
+ xtCxt->xpathobj->type == XPATH_NODESET &&
+ xtCxt->xpathobj->nodesetval != NULL);
+
+ /* Propagate context related error context to libxml2 */
+ xmlSetStructuredErrorFunc((void *) xtCxt->xmlerrcxt, xml_errorHandler);
+
+ *isnull = false;
+
+ cur = xtCxt->xpathobj->nodesetval->nodeTab[xtCxt->row_count - 1];
+
+ if (colnum == -1)
+ {
+ /*
+ * Use whole row, when user doesn't specify a column. The target type
+ * must be XMLOID.
+ */
+ Assert(state->resultSlot->tts_tupleDescriptor->attrs[0]->atttypid == XMLOID);
+
+ result = PointerGetDatum(xml_xmlnodetoxmltype(cur, xtCxt->xmlerrcxt));
+
+ return result;
+ }
+
+ Assert(xtCxt->xpathscomp[colnum] != NULL);
+
+ /* fast leaving */
+ if (cur->type != XML_ELEMENT_NODE)
+ elog(ERROR, "unexpected xmlNode type");
+
+ PG_TRY();
+ {
+ Form_pg_attribute attr;
+
+ attr = state->resultSlot->tts_tupleDescriptor->attrs[colnum];
+
+ /* Set current node as entry point for XPath evaluation */
+ xmlXPathSetContextNode(cur, xtCxt->xpathcxt);
+
+ /* Evaluate column path */
+ xpathobj = xmlXPathCompiledEval(xtCxt->xpathscomp[colnum], xtCxt->xpathcxt);
+ if (xpathobj == NULL || xtCxt->xmlerrcxt->err_occurred)
+ xml_ereport(xtCxt->xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
+ "could not create XPath object");
+
+ /*
+ * 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.
+ */
+ if (xpathobj->type == XPATH_NODESET)
+ {
+ int count = 0;
+ Oid targettypid = attr->atttypid;
+
+ if (xpathobj->nodesetval != NULL)
+ count = xpathobj->nodesetval->nodeNr;
+
+ if (xpathobj->nodesetval == NULL || count == 0)
+ {
+ *isnull = true;
+ }
+ else if (count == 1 && targettypid == 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)
+ {
+ xmlChar *str;
+
+ str = xmlNodeListGetString(xtCxt->doc,
+ xpathobj->nodesetval->nodeTab[0]->xmlChildrenNode,
+ 1);
+
+ if (str != NULL)
+ {
+ PG_TRY();
+ {
+ cstr = pstrdup((char *) str);
+ }
+ PG_CATCH();
+ {
+ xmlFree(str);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ xmlFree(str);
+ }
+ else
+ {
+ /* Return empty string when tag is empty */
+ cstr = "";
+ }
+ }
+ else
+ {
+ StringInfoData str;
+ int i;
+
+ Assert(count > 1);
+
+ /*
+ * When evaluating the XPath expression returns multiple
+ * nodes, the result is the concatenation of them all. The
+ * target type must be XML.
+ */
+ if (targettypid != XMLOID)
+ 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));
+ }
+ cstr = str.data;
+ }
+ }
+ else if (xpathobj->type == XPATH_STRING)
+ {
+ cstr = (char *) xpathobj->stringval;
+ }
+ else
+ elog(ERROR, "unexpected XPath object type %u", xpathobj->type);
+
+ /*
+ * By here, either cstr contains the result value, or the isnull flag
+ * has been set.
+ */
+ Assert(cstr || *isnull);
+
+ if (!*isnull)
+ result = InputFunctionCall(&state->in_functions[colnum],
+ cstr,
+ state->typioparams[colnum],
+ attr->atttypmod);
+ }
+ PG_CATCH();
+ {
+ if (xpathobj != NULL)
+ xmlXPathFreeObject(xpathobj);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ xmlXPathFreeObject(xpathobj);
+
+ return result;
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
+
+/*
+ * Release all libxml2 memory and related resources
+ */
+static void
+XmlTableDestroyBuilder(TableFuncScanState *state)
+{
+#ifdef USE_LIBXML
+ XmlTableBuilderData *xtCxt;
+
+ xtCxt = GetXmlTableBuilderPrivateData(state, "XmlTableDestroyBuilder");
+
+ /* Propagate context related error context to libxml2 */
+ xmlSetStructuredErrorFunc((void *) xtCxt->xmlerrcxt, xml_errorHandler);
+
+ if (xtCxt->xpathscomp != NULL)
+ {
+ int i;
+
+ for (i = 0; i < state->resultSlot->tts_tupleDescriptor->natts; i++)
+ if (xtCxt->xpathscomp[i] != NULL)
+ xmlXPathFreeCompExpr(xtCxt->xpathscomp[i]);
+ }
+
+ if (xtCxt->xpathobj != NULL)
+ xmlXPathFreeObject(xtCxt->xpathobj);
+ if (xtCxt->xpathcomp != NULL)
+ xmlXPathFreeCompExpr(xtCxt->xpathcomp);
+ if (xtCxt->xpathcxt != NULL)
+ xmlXPathFreeContext(xtCxt->xpathcxt);
+ if (xtCxt->doc != NULL)
+ xmlFreeDoc(xtCxt->doc);
+ if (xtCxt->ctxt != NULL)
+ xmlFreeParserCtxt(xtCxt->ctxt);
+
+ pg_xml_done(xtCxt->xmlerrcxt, true);
+
+ /* not valid anymore */
+ xtCxt->magic = 0;
+ state->opaque = NULL;
+
+#else
+ NO_XML_SUPPORT();
+#endif /* not USE_LIBXML */
+}
diff --git a/src/include/executor/nodeTableFuncscan.h b/src/include/executor/nodeTableFuncscan.h
new file mode 100644
index 0000000000..529c929993
--- /dev/null
+++ b/src/include/executor/nodeTableFuncscan.h
@@ -0,0 +1,24 @@
+/*-------------------------------------------------------------------------
+ *
+ * nodeTableFuncscan.h
+ *
+ *
+ *
+ * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/executor/nodeTableFuncscan.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef NODETABLEFUNCSCAN_H
+#define NODETABLEFUNCSCAN_H
+
+#include "nodes/execnodes.h"
+
+extern TableFuncScanState *ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags);
+extern TupleTableSlot *ExecTableFuncScan(TableFuncScanState *node);
+extern void ExecEndTableFuncScan(TableFuncScanState *node);
+extern void ExecReScanTableFuncScan(TableFuncScanState *node);
+
+#endif /* NODETABLEFUNCSCAN_H */
diff --git a/src/include/executor/tablefunc.h b/src/include/executor/tablefunc.h
new file mode 100644
index 0000000000..cacfeabe11
--- /dev/null
+++ b/src/include/executor/tablefunc.h
@@ -0,0 +1,65 @@
+/*-------------------------------------------------------------------------
+ *
+ * tablefunc.h
+ * interface for TableFunc builder
+ *
+ * Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/executor/tablefunc.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef TABLEFUNC_H
+#define TABLEFUNC_H
+
+#include "nodes/execnodes.h"
+
+/*
+ * TableFuncRoutine holds function pointers used for generating content of
+ * table-producer functions, such as XMLTABLE.
+ *
+ * InitBuilder initialize table builder private objects. The output tuple
+ * descriptor, input functions for the columns, and typioparams are passed
+ * from executor state.
+ *
+ * SetDoc is called to define the input document. The table builder may
+ * apply additional transformations not exposed outside the table builder
+ * context.
+ *
+ * SetNamespace is called to pass namespace declarations from the table
+ * expression. This function may be NULL if namespaces are not supported by
+ * the table builder. Namespaces must be given before setting the row and
+ * column filters. If the name is given as NULL, the entry shall be for the
+ * default namespace.
+ *
+ * SetRowFilter is called do define the row-generating filter, which shall be
+ * used to extract each row from the input document.
+ *
+ * SetColumnFilter is called once for each column, to define the column-
+ * generating filter for the given column.
+ *
+ * FetchRow shall be called repeatedly until it returns that no more rows are
+ * found in the document. On each invocation it shall set state in the table
+ * builder context such that each subsequent GetValue call returns the value
+ * for the indicated column for the row being processed.
+ *
+ * DestroyBuilder shall release all resources associated with a table builder
+ * context. It may be called either because all rows have been consumed, or
+ * because an error ocurred while processing the table expression.
+ */
+typedef struct TableFuncRoutine
+{
+ void (*InitBuilder) (TableFuncScanState *state);
+ void (*SetDoc) (TableFuncScanState *state, Datum value);
+ void (*SetNamespace) (TableFuncScanState *state, char *name,
+ char *uri);
+ void (*SetRowFilter) (TableFuncScanState *state, char *path);
+ void (*SetColumnFilter) (TableFuncScanState *state, char *path,
+ int colnum);
+ bool (*FetchRow) (TableFuncScanState *state);
+ Datum (*GetValue) (TableFuncScanState *state, int colnum, bool *isnull);
+ void (*DestroyBuilder) (TableFuncScanState *state);
+} TableFuncRoutine;
+
+#endif /* TABLEFUNC_H */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 9f41babf35..7461b8d8d0 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1582,6 +1582,35 @@ typedef struct ValuesScanState
} ValuesScanState;
/* ----------------
+ * TableFuncScanState node
+ *
+ * Used in table-expression functions like XMLTABLE.
+ * ----------------
+ */
+typedef struct TableFuncScanState
+{
+ ScanState ss; /* its first field is NodeTag */
+ ExprState *docexpr; /* state for document expression */
+ ExprState *rowexpr; /* state for row-generating expression */
+ List *colexprs; /* state for column-generating expression */
+ List *coldefexprs; /* state for column default expressions */
+ List *ns_names; /* list of str nodes with namespace names */
+ List *ns_uris; /* list of states of namespace uri exprs */
+ Bitmapset *notnulls; /* nullability flag for each output column */
+ bool evalcols; /* true, when column was not defined by user */
+ int ordinalitycol; /* number of ordinality column or -1 */
+ void *opaque; /* table builder private space */
+ const struct TableFuncRoutine *routine; /* table builder methods */
+ FmgrInfo *in_functions; /* input function for each column */
+ Oid *typioparams; /* typioparam for each column */
+ int rownum; /* row number to be output next */
+ MemoryContext buildercxt; /* memory context used by builder */
+ MemoryContext perValueCxt; /* short life context for a value evaluation */
+ Tuplestorestate *tupstore; /* output tuple store */
+ TupleTableSlot *resultSlot; /* output tuple table slot */
+} TableFuncScanState;
+
+/* ----------------
* CteScanState information
*
* CteScan nodes are used to scan a CommonTableExpr query.
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 95dd8baadd..14ee98b78b 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -61,6 +61,7 @@ typedef enum NodeTag
T_SubqueryScan,
T_FunctionScan,
T_ValuesScan,
+ T_TableFuncScan,
T_CteScan,
T_WorkTableScan,
T_ForeignScan,
@@ -109,6 +110,7 @@ typedef enum NodeTag
T_TidScanState,
T_SubqueryScanState,
T_FunctionScanState,
+ T_TableFuncScanState,
T_ValuesScanState,
T_CteScanState,
T_WorkTableScanState,
@@ -135,6 +137,7 @@ typedef enum NodeTag
*/
T_Alias,
T_RangeVar,
+ T_TableFunc,
T_Expr,
T_Var,
T_Const,
@@ -438,6 +441,8 @@ typedef enum NodeTag
T_RangeSubselect,
T_RangeFunction,
T_RangeTableSample,
+ T_RangeTableFunc,
+ T_RangeTableFuncCol,
T_TypeName,
T_ColumnDef,
T_IndexElem,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5afc3ebea0..e7f4205c00 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -575,6 +575,40 @@ typedef struct RangeTableSample
} RangeTableSample;
/*
+ * RangeTableFunc - a raw form of pseudo functions like XMLTABLE
+ */
+typedef struct RangeTableFunc
+{
+ NodeTag type;
+ bool lateral; /* does it have LATERAL prefix? */
+ Node *docexpr;
+ Node *rowexpr;
+ List *namespaces; /* list of namespaces */
+ List *columns; /* list of columns (TableFuncCol) */
+ Alias *alias; /* table alias & optional column aliases */
+ int location; /* token location, or -1 if unknown */
+} RangeTableFunc;
+
+/*
+ * RangeTableFuncCol - one column in a RangeTableFunc column list,
+ * in raw form.
+ *
+ * If for_ordinality is true (FOR ORDINALITY), then the column is an int4
+ * column and the rest of the fields are ignored.
+ */
+typedef struct RangeTableFuncCol
+{
+ NodeTag type;
+ char *colname; /* name of generated column */
+ TypeName *typeName; /* type of generated column */
+ bool for_ordinality; /* whether this is FOR ORDINALITY */
+ bool is_not_null; /* nullability flag */
+ Node *colexpr; /* column filter expression */
+ Node *coldefexpr; /* column default value expr */
+ int location; /* token location, or -1 if unknown */
+} RangeTableFuncCol;
+
+/*
* ColumnDef - column definition (used in various creates)
*
* If the column has a default value, we may have the value expression
@@ -871,6 +905,7 @@ typedef enum RTEKind
RTE_SUBQUERY, /* subquery in FROM */
RTE_JOIN, /* join */
RTE_FUNCTION, /* function in FROM */
+ RTE_TABLEFUNC, /* TableFunc(.., column list) */
RTE_VALUES, /* VALUES (<exprlist>), (<exprlist>), ... */
RTE_CTE /* common table expr (WITH list element) */
} RTEKind;
@@ -932,6 +967,11 @@ typedef struct RangeTblEntry
bool funcordinality; /* is this called WITH ORDINALITY? */
/*
+ * Fields valid for a TableFunc RTE (else NIL/zero)
+ */
+ TableFunc *tablefunc;
+
+ /*
* Fields valid for a values RTE (else NIL):
*/
List *values_lists; /* list of expression lists */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index f72f7a8978..c6bcc14411 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -496,6 +496,16 @@ typedef struct ValuesScan
} ValuesScan;
/* ----------------
+ * TableFunc scan node
+ * ----------------
+ */
+typedef struct TableFuncScan
+{
+ Scan scan;
+ TableFunc *tablefunc; /* table function node */
+} TableFuncScan;
+
+/* ----------------
* CteScan node
* ----------------
*/
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 235bc75096..e7e568ae42 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -18,6 +18,7 @@
#define PRIMNODES_H
#include "access/attnum.h"
+#include "nodes/bitmapset.h"
#include "nodes/pg_list.h"
@@ -73,6 +74,29 @@ typedef struct RangeVar
} RangeVar;
/*
+ * TableFunc - node for a table function, such as XMLTABLE.
+ */
+typedef struct TableFunc
+{
+ NodeTag type;
+ List *ns_uris; /* list of namespace uri */
+ List *ns_names; /* list of namespace names */
+ Node *docexpr; /* input document expression */
+ Node *rowexpr; /* row filter expression */
+ int colcount; /* number of output columns */
+ List *colnames; /* column names (list of String) */
+ List *coltypes; /* OID list of column type OIDs */
+ List *coltypmods; /* integer list of column typmods */
+ List *colcollations; /* OID list of column collation OIDs */
+ List *colexprs; /* list of column filter expressions */
+ List *coldefexprs; /* list of column default expressions */
+ Bitmapset *notnulls; /* nullability flag for each output column */
+ int ordinalitycol; /* ordinality column, -1 if not used */
+ bool evalcols; /* true, when columns should be evaluated */
+ int location; /* token location, or -1 if unknown */
+} TableFunc;
+
+/*
* IntoClause - target information for SELECT INTO, CREATE TABLE AS, and
* CREATE MATERIALIZED VIEW
*
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 72200fa531..d02aba1559 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -89,8 +89,12 @@ extern void cost_subqueryscan(SubqueryScanPath *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info);
extern void cost_functionscan(Path *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info);
+extern void cost_tableexprscan(Path *path, PlannerInfo *root,
+ RelOptInfo *baserel, ParamPathInfo *param_info);
extern void cost_valuesscan(Path *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info);
+extern void cost_tablefuncscan(Path *path, PlannerInfo *root,
+ RelOptInfo *baserel, ParamPathInfo *param_info);
extern void cost_ctescan(Path *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info);
extern void cost_recursive_union(Path *runion, Path *nrterm, Path *rterm);
@@ -181,6 +185,7 @@ extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
double cte_rows);
+extern void set_tablefunc_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern void set_foreign_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern PathTarget *set_pathtarget_cost_width(PlannerInfo *root, PathTarget *target);
extern double compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 53cad247dc..4588680f4f 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -82,8 +82,12 @@ extern SubqueryScanPath *create_subqueryscan_path(PlannerInfo *root,
List *pathkeys, Relids required_outer);
extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel,
List *pathkeys, Relids required_outer);
+extern Path *create_tablexprscan_path(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys, Relids required_outer);
extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
+extern Path *create_tablefuncscan_path(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys, Relids required_outer);
extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_worktablescan_path(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 985d6505ec..28c4dab258 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -82,6 +82,7 @@ PG_KEYWORD("coalesce", COALESCE, COL_NAME_KEYWORD)
PG_KEYWORD("collate", COLLATE, RESERVED_KEYWORD)
PG_KEYWORD("collation", COLLATION, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("column", COLUMN, RESERVED_KEYWORD)
+PG_KEYWORD("columns", COLUMNS, UNRESERVED_KEYWORD)
PG_KEYWORD("comment", COMMENT, UNRESERVED_KEYWORD)
PG_KEYWORD("comments", COMMENTS, UNRESERVED_KEYWORD)
PG_KEYWORD("commit", COMMIT, UNRESERVED_KEYWORD)
@@ -446,10 +447,12 @@ PG_KEYWORD("xmlconcat", XMLCONCAT, COL_NAME_KEYWORD)
PG_KEYWORD("xmlelement", XMLELEMENT, COL_NAME_KEYWORD)
PG_KEYWORD("xmlexists", XMLEXISTS, COL_NAME_KEYWORD)
PG_KEYWORD("xmlforest", XMLFOREST, COL_NAME_KEYWORD)
+PG_KEYWORD("xmlnamespaces", XMLNAMESPACES, COL_NAME_KEYWORD)
PG_KEYWORD("xmlparse", XMLPARSE, COL_NAME_KEYWORD)
PG_KEYWORD("xmlpi", XMLPI, COL_NAME_KEYWORD)
PG_KEYWORD("xmlroot", XMLROOT, COL_NAME_KEYWORD)
PG_KEYWORD("xmlserialize", XMLSERIALIZE, COL_NAME_KEYWORD)
+PG_KEYWORD("xmltable", XMLTABLE, COL_NAME_KEYWORD)
PG_KEYWORD("year", YEAR_P, UNRESERVED_KEYWORD)
PG_KEYWORD("yes", YES_P, UNRESERVED_KEYWORD)
PG_KEYWORD("zone", ZONE, UNRESERVED_KEYWORD)
diff --git a/src/include/parser/parse_coerce.h b/src/include/parser/parse_coerce.h
index b50992cfd9..3eed81966d 100644
--- a/src/include/parser/parse_coerce.h
+++ b/src/include/parser/parse_coerce.h
@@ -58,6 +58,10 @@ extern Node *coerce_to_specific_type(ParseState *pstate, Node *node,
Oid targetTypeId,
const char *constructName);
+extern Node *coerce_to_specific_type_typmod(ParseState *pstate, Node *node,
+ Oid targetTypeId, int32 targetTypmod,
+ const char *constructName);
+
extern int parser_coercion_errposition(ParseState *pstate,
int coerce_location,
Node *input_expr);
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index cfb2e5b88c..dcb6194b1a 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -91,6 +91,11 @@ extern RangeTblEntry *addRangeTableEntryForValues(ParseState *pstate,
Alias *alias,
bool lateral,
bool inFromCl);
+extern RangeTblEntry *addRangeTableEntryForTableFunc(ParseState *pstate,
+ TableFunc *tf,
+ Alias *alias,
+ bool lateral,
+ bool inFromCl);
extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate,
List *colnames,
JoinType jointype,
diff --git a/src/include/utils/xml.h b/src/include/utils/xml.h
index cc1fc390e8..e570b71c04 100644
--- a/src/include/utils/xml.h
+++ b/src/include/utils/xml.h
@@ -18,6 +18,7 @@
#include "fmgr.h"
#include "nodes/execnodes.h"
#include "nodes/primnodes.h"
+#include "executor/tablefunc.h"
typedef struct varlena xmltype;
@@ -76,4 +77,6 @@ extern int xmlbinary; /* XmlBinaryType, but int for guc enum */
extern int xmloption; /* XmlOptionType, but int for guc enum */
+extern const TableFuncRoutine XmlTableRoutine;
+
#endif /* XML_H */
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index f21e119f1e..84ccd33a10 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -948,3 +948,524 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>
(1 row)
+-- XMLPATH tests
+CREATE TABLE xmldata(data xml);
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="1">
+ <COUNTRY_ID>AU</COUNTRY_ID>
+ <COUNTRY_NAME>Australia</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="2">
+ <COUNTRY_ID>CN</COUNTRY_ID>
+ <COUNTRY_NAME>China</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="3">
+ <COUNTRY_ID>HK</COUNTRY_ID>
+ <COUNTRY_NAME>HongKong</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="4">
+ <COUNTRY_ID>IN</COUNTRY_ID>
+ <COUNTRY_NAME>India</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="5">
+ <COUNTRY_ID>JP</COUNTRY_ID>
+ <COUNTRY_NAME>Japan</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+</ROW>
+<ROW id="6">
+ <COUNTRY_ID>SG</COUNTRY_ID>
+ <COUNTRY_NAME>Singapore</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
+</ROW>
+</ROWS>');
+-- XMLTABLE without columns
+SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+ xmltable
+-------------
+ <row> +
+ <a>10</a>+
+ <a>20</a>+
+ </row>
+(1 row)
+
+-- XMLTABLE with columns
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+CREATE VIEW xmltableview1 AS SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+SELECT * FROM xmltableview1;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+\sv xmltableview1
+CREATE OR REPLACE VIEW public.xmltableview1 AS
+ SELECT "xmltable".id,
+ "xmltable"._id,
+ "xmltable".country_name,
+ "xmltable".country_id,
+ "xmltable".region_id,
+ "xmltable".size,
+ "xmltable".unit,
+ "xmltable".premier_name
+ FROM ( SELECT xmldata.data
+ FROM xmldata) x,
+ LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on xmldata
+ -> TableFunc Scan on "xmltable"
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- XMLNAMESPACES tests
+SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+ a
+----
+ 10
+(1 row)
+
+CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+SELECT * FROM xmltableview2;
+ a
+----
+ 10
+(1 row)
+
+SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
+ '/rows/row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'a');
+ERROR: DEFAULT namespace is not supported
+SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+ xmltable
+----------
+ 10
+ 20
+(2 rows)
+
+-- used in prepare statements
+PREPARE pp AS
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+EXECUTE pp;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+ India | 3
+ Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+ 1 | India | 3
+ 2 | Japan | 3
+(2 rows)
+
+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);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+ 4 | India | 3
+ 5 | Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
+ id
+----
+ 4
+ 5
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
+ id
+----
+ 1
+ 2
+(2 rows)
+
+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 '.');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+------------------------------------------------------------------
+ 4 | India | 3 | <ROW id="4"> +
+ | | | <COUNTRY_ID>IN</COUNTRY_ID> +
+ | | | <COUNTRY_NAME>India</COUNTRY_NAME> +
+ | | | <REGION_ID>3</REGION_ID> +
+ | | | </ROW>
+ 5 | Japan | 3 | <ROW id="5"> +
+ | | | <COUNTRY_ID>JP</COUNTRY_ID> +
+ | | | <COUNTRY_NAME>Japan</COUNTRY_NAME> +
+ | | | <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>+
+ | | | </ROW>
+(2 rows)
+
+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 './*');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------
+ 4 | India | 3 | <COUNTRY_ID>IN</COUNTRY_ID><COUNTRY_NAME>India</COUNTRY_NAME><REGION_ID>3</REGION_ID>
+ 5 | Japan | 3 | <COUNTRY_ID>JP</COUNTRY_ID><COUNTRY_NAME>Japan</COUNTRY_NAME><REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+(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
+(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
+ERROR: more than one value returned by column XPath expression
+-- CDATA test
+select * from xmltable('r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
+ c
+-------------------------
+ <hello> &"<>!<a>foo</a>
+ 2
+(2 rows)
+
+-- 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);
+ ent
+-----
+ '
+ "
+ &
+ <
+ >
+(5 rows)
+
+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);
+ ent
+------------------
+ <ent>'</ent>
+ <ent>"</ent>
+ <ent>&</ent>
+ <ent><</ent>
+ <ent>></ent>
+(5 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- test qual
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+ Japan | 3
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
+ Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+(8 rows)
+
+-- should to work with more data
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="10">
+ <COUNTRY_ID>CZ</COUNTRY_ID>
+ <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
+</ROW>
+<ROW id="11">
+ <COUNTRY_ID>DE</COUNTRY_ID>
+ <COUNTRY_NAME>Germany</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+<ROW id="12">
+ <COUNTRY_ID>FR</COUNTRY_ID>
+ <COUNTRY_NAME>France</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+</ROWS>');
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="20">
+ <COUNTRY_ID>EG</COUNTRY_ID>
+ <COUNTRY_NAME>Egypt</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+<ROW id="21">
+ <COUNTRY_ID>SD</COUNTRY_ID>
+ <COUNTRY_NAME>Sudan</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+</ROWS>');
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+----------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+ 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
+ 11 | 2 | Germany | DE | 2 | | | not specified
+ 12 | 3 | France | FR | 2 | | | not specified
+ 20 | 1 | Egypt | EG | 1 | | | not specified
+ 21 | 2 | Sudan | SD | 1 | | | not specified
+(11 rows)
+
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+----------------+------------+-----------+------+------+---------------
+ 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
+ 11 | 2 | Germany | DE | 2 | | | not specified
+ 12 | 3 | France | FR | 2 | | | not specified
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+ Filter: ("xmltable".region_id = 2)
+(8 rows)
+
+-- should fail, NULL value
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE' NOT NULL,
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ERROR: null is not allowed in column "size"
+-- if all is ok, then result is empty
+-- one line xml test
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc WHERE proname = 'f_leak'),
+ y AS (SELECT xmlelement(name proc,
+ xmlforest(proname, proowner,
+ procost, pronargs,
+ proargnames, proargtypes)) as proc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/proc' PASSING proc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ proname | proowner | procost | pronargs | proargnames | proargtypes
+---------+----------+---------+----------+-------------+-------------
+(0 rows)
+
+-- multi line xml test, result should be empty too
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc),
+ y AS (SELECT xmlelement(name data,
+ xmlagg(xmlelement(name proc,
+ xmlforest(proname, proowner, procost,
+ pronargs, proargnames, proargtypes)))) as doc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/data/proc' PASSING doc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ proname | proowner | procost | pronargs | proargnames | proargtypes
+---------+----------+---------+----------+-------------+-------------
+(0 rows)
+
+CREATE TABLE xmltest2(x xml, _path text);
+INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
+INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
+INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
+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');
+ a
+---
+ 1
+ 2
+ 3
+ 2
+(4 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
+ a
+---
+ 1
+ 2
+ 3
+ 2
+(4 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
+ a
+----
+ 11
+ 12
+ 13
+ 14
+(4 rows)
+
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index d7027030c3..556c64b73a 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -827,3 +827,491 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4
ERROR: unsupported XML feature
DETAIL: This functionality requires the server to be built with libxml support.
HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- XMLPATH tests
+CREATE TABLE xmldata(data xml);
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="1">
+ <COUNTRY_ID>AU</COUNTRY_ID>
+ <COUNTRY_NAME>Australia</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="2">
+ <COUNTRY_ID>CN</COUNTRY_ID>
+ <COUNTRY_NAME>China</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="3">
+ <COUNTRY_ID>HK</COUNTRY_ID>
+ <COUNTRY_NAME>HongKong</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="4">
+ <COUNTRY_ID>IN</COUNTRY_ID>
+ <COUNTRY_NAME>India</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="5">
+ <COUNTRY_ID>JP</COUNTRY_ID>
+ <COUNTRY_NAME>Japan</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+</ROW>
+<ROW id="6">
+ <COUNTRY_ID>SG</COUNTRY_ID>
+ <COUNTRY_NAME>Singapore</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
+</ROW>
+</ROWS>');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmldata VALUES('<ROWS>
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- XMLTABLE without columns
+SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- XMLTABLE with columns
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+--------------
+(0 rows)
+
+CREATE VIEW xmltableview1 AS SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+SELECT * FROM xmltableview1;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+--------------
+(0 rows)
+
+\sv xmltableview1
+CREATE OR REPLACE VIEW public.xmltableview1 AS
+ SELECT "xmltable".id,
+ "xmltable"._id,
+ "xmltable".country_name,
+ "xmltable".country_id,
+ "xmltable".region_id,
+ "xmltable".size,
+ "xmltable".unit,
+ "xmltable".premier_name
+ FROM ( SELECT xmldata.data
+ FROM xmldata) x,
+ LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on xmldata
+ -> TableFunc Scan on "xmltable"
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- XMLNAMESPACES tests
+SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+ERROR: unsupported XML feature
+LINE 3: PASSING '<rows xmlns="http://x.y"><row...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+ERROR: unsupported XML feature
+LINE 3: PASSING '<rows xmlns="http://x.y"><row...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT * FROM xmltableview2;
+ERROR: relation "xmltableview2" does not exist
+LINE 1: SELECT * FROM xmltableview2;
+ ^
+SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
+ '/rows/row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'a');
+ERROR: unsupported XML feature
+LINE 3: PASSING '<rows xmlns="http://x.y"><row...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+ERROR: unsupported XML feature
+LINE 1: ...LECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><ro...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- used in prepare statements
+PREPARE pp AS
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+EXECUTE pp;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+--------------
+(0 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+(0 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+(0 rows)
+
+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);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+(0 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
+ id
+----
+(0 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
+ id
+----
+(0 rows)
+
+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 '.');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+---------
+(0 rows)
+
+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 './*');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+---------
+(0 rows)
+
+SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM xmltable('/root' passing '<root><element>a1a<!...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+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
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM xmltable('/root' passing '<root><element>a1a<!...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- CDATA test
+select * from xmltable('r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
+ERROR: unsupported XML feature
+LINE 1: select * from xmltable('r' passing '<d><r><c><![CDATA[<hello...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- 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);
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</en...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+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);
+ERROR: unsupported XML feature
+LINE 1: SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</en...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- test qual
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+(0 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
+ Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+(8 rows)
+
+-- should to work with more data
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="10">
+ <COUNTRY_ID>CZ</COUNTRY_ID>
+ <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
+</ROW>
+<ROW id="11">
+ <COUNTRY_ID>DE</COUNTRY_ID>
+ <COUNTRY_NAME>Germany</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+<ROW id="12">
+ <COUNTRY_ID>FR</COUNTRY_ID>
+ <COUNTRY_NAME>France</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+</ROWS>');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmldata VALUES('<ROWS>
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="20">
+ <COUNTRY_ID>EG</COUNTRY_ID>
+ <COUNTRY_NAME>Egypt</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+<ROW id="21">
+ <COUNTRY_ID>SD</COUNTRY_ID>
+ <COUNTRY_NAME>Sudan</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+</ROWS>');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmldata VALUES('<ROWS>
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+--------------
+(0 rows)
+
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+--------------
+(0 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+ Filter: ("xmltable".region_id = 2)
+(8 rows)
+
+-- should fail, NULL value
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE' NOT NULL,
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+--------------
+(0 rows)
+
+-- if all is ok, then result is empty
+-- one line xml test
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc WHERE proname = 'f_leak'),
+ y AS (SELECT xmlelement(name proc,
+ xmlforest(proname, proowner,
+ procost, pronargs,
+ proargnames, proargtypes)) as proc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/proc' PASSING proc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ERROR: unsupported XML feature
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+-- multi line xml test, result should be empty too
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc),
+ y AS (SELECT xmlelement(name data,
+ xmlagg(xmlelement(name proc,
+ xmlforest(proname, proowner, procost,
+ pronargs, proargnames, proargtypes)))) as doc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/data/proc' PASSING doc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ERROR: unsupported XML feature
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+CREATE TABLE xmltest2(x xml, _path text);
+INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A')...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B')...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C')...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D');
+ERROR: unsupported XML feature
+LINE 1: INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D')...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+HINT: You need to rebuild PostgreSQL using --with-libxml.
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c');
+ a
+---
+(0 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
+ a
+---
+(0 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
+ a
+---
+(0 rows)
+
diff --git a/src/test/regress/expected/xml_2.out b/src/test/regress/expected/xml_2.out
index 530faf5daf..fe7a25da87 100644
--- a/src/test/regress/expected/xml_2.out
+++ b/src/test/regress/expected/xml_2.out
@@ -928,3 +928,524 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>
(1 row)
+-- XMLPATH tests
+CREATE TABLE xmldata(data xml);
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="1">
+ <COUNTRY_ID>AU</COUNTRY_ID>
+ <COUNTRY_NAME>Australia</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="2">
+ <COUNTRY_ID>CN</COUNTRY_ID>
+ <COUNTRY_NAME>China</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="3">
+ <COUNTRY_ID>HK</COUNTRY_ID>
+ <COUNTRY_NAME>HongKong</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="4">
+ <COUNTRY_ID>IN</COUNTRY_ID>
+ <COUNTRY_NAME>India</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="5">
+ <COUNTRY_ID>JP</COUNTRY_ID>
+ <COUNTRY_NAME>Japan</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+</ROW>
+<ROW id="6">
+ <COUNTRY_ID>SG</COUNTRY_ID>
+ <COUNTRY_NAME>Singapore</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
+</ROW>
+</ROWS>');
+-- XMLTABLE without columns
+SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+ xmltable
+-------------
+ <row> +
+ <a>10</a>+
+ <a>20</a>+
+ </row>
+(1 row)
+
+-- XMLTABLE with columns
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+CREATE VIEW xmltableview1 AS SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+SELECT * FROM xmltableview1;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+\sv xmltableview1
+CREATE OR REPLACE VIEW public.xmltableview1 AS
+ SELECT "xmltable".id,
+ "xmltable"._id,
+ "xmltable".country_name,
+ "xmltable".country_id,
+ "xmltable".region_id,
+ "xmltable".size,
+ "xmltable".unit,
+ "xmltable".premier_name
+ FROM ( SELECT xmldata.data
+ FROM xmldata) x,
+ LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on xmldata
+ -> TableFunc Scan on "xmltable"
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- XMLNAMESPACES tests
+SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+ a
+----
+ 10
+(1 row)
+
+CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+SELECT * FROM xmltableview2;
+ a
+----
+ 10
+(1 row)
+
+SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
+ '/rows/row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'a');
+ERROR: DEFAULT namespace is not supported
+SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+ xmltable
+----------
+ 10
+ 20
+(2 rows)
+
+-- used in prepare statements
+PREPARE pp AS
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+EXECUTE pp;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+ India | 3
+ Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+ 1 | India | 3
+ 2 | Japan | 3
+(2 rows)
+
+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);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+ 4 | India | 3
+ 5 | Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
+ id
+----
+ 4
+ 5
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
+ id
+----
+ 1
+ 2
+(2 rows)
+
+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 '.');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+------------------------------------------------------------------
+ 4 | India | 3 | <ROW id="4"> +
+ | | | <COUNTRY_ID>IN</COUNTRY_ID> +
+ | | | <COUNTRY_NAME>India</COUNTRY_NAME> +
+ | | | <REGION_ID>3</REGION_ID> +
+ | | | </ROW>
+ 5 | Japan | 3 | <ROW id="5"> +
+ | | | <COUNTRY_ID>JP</COUNTRY_ID> +
+ | | | <COUNTRY_NAME>Japan</COUNTRY_NAME> +
+ | | | <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>+
+ | | | </ROW>
+(2 rows)
+
+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 './*');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------
+ 4 | India | 3 | <COUNTRY_ID>IN</COUNTRY_ID><COUNTRY_NAME>India</COUNTRY_NAME><REGION_ID>3</REGION_ID>
+ 5 | Japan | 3 | <COUNTRY_ID>JP</COUNTRY_ID><COUNTRY_NAME>Japan</COUNTRY_NAME><REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+(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
+(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
+ERROR: more than one value returned by column XPath expression
+-- CDATA test
+select * from xmltable('r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
+ c
+-------------------------
+ <hello> &"<>!<a>foo</a>
+ 2
+(2 rows)
+
+-- 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);
+ ent
+-----
+ '
+ "
+ &
+ <
+ >
+(5 rows)
+
+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);
+ ent
+------------------
+ <ent>'</ent>
+ <ent>"</ent>
+ <ent>&</ent>
+ <ent><</ent>
+ <ent>></ent>
+(5 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- test qual
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+ Japan | 3
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
+ Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+(8 rows)
+
+-- should to work with more data
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="10">
+ <COUNTRY_ID>CZ</COUNTRY_ID>
+ <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
+</ROW>
+<ROW id="11">
+ <COUNTRY_ID>DE</COUNTRY_ID>
+ <COUNTRY_NAME>Germany</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+<ROW id="12">
+ <COUNTRY_ID>FR</COUNTRY_ID>
+ <COUNTRY_NAME>France</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+</ROWS>');
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="20">
+ <COUNTRY_ID>EG</COUNTRY_ID>
+ <COUNTRY_NAME>Egypt</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+<ROW id="21">
+ <COUNTRY_ID>SD</COUNTRY_ID>
+ <COUNTRY_NAME>Sudan</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+</ROWS>');
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+----------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+ 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
+ 11 | 2 | Germany | DE | 2 | | | not specified
+ 12 | 3 | France | FR | 2 | | | not specified
+ 20 | 1 | Egypt | EG | 1 | | | not specified
+ 21 | 2 | Sudan | SD | 1 | | | not specified
+(11 rows)
+
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+----------------+------------+-----------+------+------+---------------
+ 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
+ 11 | 2 | Germany | DE | 2 | | | not specified
+ 12 | 3 | France | FR | 2 | | | not specified
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> TableFunc Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+ Filter: ("xmltable".region_id = 2)
+(8 rows)
+
+-- should fail, NULL value
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE' NOT NULL,
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ERROR: null is not allowed in column "size"
+-- if all is ok, then result is empty
+-- one line xml test
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc WHERE proname = 'f_leak'),
+ y AS (SELECT xmlelement(name proc,
+ xmlforest(proname, proowner,
+ procost, pronargs,
+ proargnames, proargtypes)) as proc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/proc' PASSING proc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ proname | proowner | procost | pronargs | proargnames | proargtypes
+---------+----------+---------+----------+-------------+-------------
+(0 rows)
+
+-- multi line xml test, result should be empty too
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc),
+ y AS (SELECT xmlelement(name data,
+ xmlagg(xmlelement(name proc,
+ xmlforest(proname, proowner, procost,
+ pronargs, proargnames, proargtypes)))) as doc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/data/proc' PASSING doc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ proname | proowner | procost | pronargs | proargnames | proargtypes
+---------+----------+---------+----------+-------------+-------------
+(0 rows)
+
+CREATE TABLE xmltest2(x xml, _path text);
+INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
+INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
+INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
+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');
+ a
+---
+ 1
+ 2
+ 3
+ 2
+(4 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
+ a
+---
+ 1
+ 2
+ 3
+ 2
+(4 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
+ a
+----
+ 11
+ 12
+ 13
+ 14
+(4 rows)
+
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 08a0b30067..af635b945c 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -270,3 +270,296 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>
SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>');
-- This might or might not load the requested DTD, but it mustn't throw error.
SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>');
+
+-- XMLPATH tests
+CREATE TABLE xmldata(data xml);
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="1">
+ <COUNTRY_ID>AU</COUNTRY_ID>
+ <COUNTRY_NAME>Australia</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="2">
+ <COUNTRY_ID>CN</COUNTRY_ID>
+ <COUNTRY_NAME>China</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="3">
+ <COUNTRY_ID>HK</COUNTRY_ID>
+ <COUNTRY_NAME>HongKong</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="4">
+ <COUNTRY_ID>IN</COUNTRY_ID>
+ <COUNTRY_NAME>India</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="5">
+ <COUNTRY_ID>JP</COUNTRY_ID>
+ <COUNTRY_NAME>Japan</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+</ROW>
+<ROW id="6">
+ <COUNTRY_ID>SG</COUNTRY_ID>
+ <COUNTRY_NAME>Singapore</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
+</ROW>
+</ROWS>');
+
+-- XMLTABLE without columns
+SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+
+-- XMLTABLE with columns
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+CREATE VIEW xmltableview1 AS SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+SELECT * FROM xmltableview1;
+
+\sv xmltableview1
+
+EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
+
+-- XMLNAMESPACES tests
+SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+
+CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+
+SELECT * FROM xmltableview2;
+
+SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
+ '/rows/row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'a');
+
+SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
+
+-- used in prepare statements
+PREPARE pp AS
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+EXECUTE pp;
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
+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);
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
+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?> <!--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
+
+-- CDATA test
+select * from xmltable('r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
+
+-- 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);
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+-- test qual
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+
+-- should to work with more data
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="10">
+ <COUNTRY_ID>CZ</COUNTRY_ID>
+ <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
+</ROW>
+<ROW id="11">
+ <COUNTRY_ID>DE</COUNTRY_ID>
+ <COUNTRY_NAME>Germany</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+<ROW id="12">
+ <COUNTRY_ID>FR</COUNTRY_ID>
+ <COUNTRY_NAME>France</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+</ROWS>');
+
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="20">
+ <COUNTRY_ID>EG</COUNTRY_ID>
+ <COUNTRY_NAME>Egypt</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+<ROW id="21">
+ <COUNTRY_ID>SD</COUNTRY_ID>
+ <COUNTRY_NAME>Sudan</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+</ROWS>');
+
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+
+-- should fail, NULL value
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE' NOT NULL,
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+
+-- if all is ok, then result is empty
+-- one line xml test
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc WHERE proname = 'f_leak'),
+ y AS (SELECT xmlelement(name proc,
+ xmlforest(proname, proowner,
+ procost, pronargs,
+ proargnames, proargtypes)) as proc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/proc' PASSING proc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+
+-- multi line xml test, result should be empty too
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ array_to_string(proargtypes,',') as proargtypes
+ FROM pg_proc),
+ y AS (SELECT xmlelement(name data,
+ xmlagg(xmlelement(name proc,
+ xmlforest(proname, proowner, procost,
+ pronargs, proargnames, proargtypes)))) as doc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/data/proc' PASSING doc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+
+CREATE TABLE xmltest2(x xml, _path text);
+
+INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
+INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
+INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
+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);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9f876ae264..990b2f7285 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1,3 +1,15 @@
+XmlTableBuilderData
+TableFuncRoutine
+XmlTableContext
+TableFuncBuilder
+TableFuncState
+TableFuncRawCol
+TableFunc
+TableFuncColumn
+SQLValueFunction
+max_parallel_hazard_context
+TriggerTransition
+SQLValueFunctionOp
ABITVEC
ACCESS_ALLOWED_ACE
ACL_SIZE_INFORMATION