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(' Processing XML - - XPath - - To process values of data type xml, PostgreSQL offers - the functions xpath and + the functions xpath, xpath_exists, which evaluate XPath 1.0 - expressions. + expressions, and the XMLTABLE predicate. + + <literal>xpath</literal> + + + XPath + + xpath(xpath, xml , nsarray) - - The function xpath evaluates the XPath - expression xpath (a text value) - against the XML value - xml. 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. - + + The function xpath evaluates the XPath + expression xpath (a text value) + against the XML value + xml. 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. + - - The second argument must be a well formed XML document. In particular, - it must have a single root node element. - + + The second argument must be a well formed XML document. In particular, + it must have a single root node element. + - - The optional third argument of the function is an array of namespace - mappings. This array should be a two-dimensional 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 xpath - function context, aliases are local). - + + The optional third argument of the function is an array of namespace + mappings. This array should be a two-dimensional 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 xpath + function context, aliases are local). + - - Example: + + Example: test', ARRAY[ARRAY['my', 'http://example.com']]); @@ -10378,10 +10381,10 @@ SELECT xpath('/my:a/text()', 'test', {test} (1 row) ]]> - + - - To deal with default (anonymous) namespaces, do something like this: + + To deal with default (anonymous) namespaces, do something like this: test', ARRAY[ARRAY['mydefns', 'http://example.com']]); @@ -10391,27 +10394,31 @@ SELECT xpath('//mydefns:b/text()', 'test - + + - - xpath_exists - + + <literal>xpath_exists</literal> + + + xpath_exists + xpath_exists(xpath, xml , nsarray) - - The function xpath_exists is a specialized form - of the xpath function. Instead of returning the - individual XML values that satisfy the XPath, this function returns a - Boolean indicating whether the query was satisfied or not. This - function is equivalent to the standard XMLEXISTS predicate, - except that it also offers support for a namespace mapping argument. - + + The function xpath_exists is a specialized form + of the xpath function. Instead of returning the + individual XML values that satisfy the XPath, this function returns a + Boolean indicating whether the query was satisfied or not. This + function is equivalent to the standard XMLEXISTS predicate, + except that it also offers support for a namespace mapping argument. + - - Example: + + Example: test', ARRAY[ARRAY['my', 'http://example.com']]); @@ -10421,7 +10428,278 @@ SELECT xpath_exists('/my:a/text()', 'test + + + + + <literal>xmltable</literal> + + + xmltable + + + +xmltable( + xmlnamespaces(namespace uri AS namespace name, ...) + rowexpr PASSING BY REF xml BY REF + COLUMNS name type + PATH columnexpr + DEFAULT expr + NOT NULL | NULL , ... +) + + + + The xmltable produces a table based on the + passed XML value, an xpath filter to extract rows, and an + optional set of column definitions. + + + + For XML document: + + + AU + Australia + 3 + + + CN + China + 3 + + + HK + HongKong + 3 + + + IN + India + 3 + + + JP + Japan + 3Sinzo Abe + + + SG + Singapore + 3791 + + +]]> + + the following query produces the result: + + + + + + The optional xmlnamespaces clause is a comma-separated list of + namespaces of the form namespace-URI AS + namespace-name. It specifies the XML namespaces used in + the document and their aliases. The default namespace is not supported + yet. + +10' + COLUMNS a int PATH 'a'); + + a +---- + 10 +]]> + + + The required 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 + xmltable transforms into output rows. + + + + + The SQL/XML standard specifies the the + xmltable construct should take an XQuery + expression as the first argument, but PostgreSQL currently only + supports XPath, which is a subset of XQuery. + + + + + The PASSING xml clause provides the + XML document to operate on. + The BY REF clauses have no effect in + PostgreSQL, but are allowed for SQL conformance and compatibility + with other implementations. Per the SQL/XML standard, the first + BY REF is required, the second is optional. + Passing BY VALUE is not supported. Multiple + comma-separated terms in the PASSING clause are not supported. + AS aliases are not supported. The argument must be + a well-formed XML document; fragments/forests are not accepted. + + + + The optional COLUMNS clause specifies the list + of colums in the generated table. If the whole + COLUMNS list is omitted, then the result set is a + single column where each row is a field of xml type + containing the data matched by the 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. + 10'); + + xmltable +------------------------------ + 10 +]]> + + + + + 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. + + + + + The PATH for a column is an xpath expression that is + evaluated for each row, relative to the result of the + rowexpr, to find the value of the column. + If no PATH is given then the column name is used as an + implicit path. + + + + The text body of the XML matched by the PATH expression is + used as the column value. Multiple text() nodes within + an element are concatenated in order. Any child elements, processing + instructions, and comments are ignored, but the text contents of child + elements are concatenated to the result: + a1aa2a bbbbxxxcccc' COLUMNS element text); +element +----------------- + a1aa2a bbbbcccc + (1 row) +]]> + Note that the whitespace-only text() node between two non-text + elements is preserved, and that leading whitespace on a text() + node is not flattened. + + + + A PATH expression that matches multiple top-level elements + results in an error: + 12' COLUMNS element text); +ERROR: more than one value returned by column XPath expression +]]> + This applies to text() nodes too, so avoid using explicit text nodes + in your path expressions unless this behaviour is desired: + a1aa2a' COLUMNS element text PATH 'element/text()'); +ERROR: more than one value returned by column XPath expression +]]> + If the PATH matches an empty tag the result is an empty string + (not NULL). Any xsi:nil attributes are ignored. + + + + The five predefined XML entities 'apos;, + 'quot;, 'amp;, 'lt; + and 'gt; are expanded to their corresponding characters + ', ", &, < + and > on processing. If the output column format is + xml then the characters <, > and + & are converted to XML entities, but " and + ' are left unchanged. This means that 'apos; + and 'quot; entities in XML input get expanded in xml + output columns. There is no way to prevent their expansion. + + + + If the path expression does not match for a given row but a + DEFAULT expression is specified, the resulting + default value is used. If no DEFAULT is given the + field will be NULL. Unlike normal queries, it is possible for + a DEFAULT expression to reference the value of output columns + that appear prior to it in the column-list, so the default of one + column may be based on the value of another column. + + + + Columns may be marked NOT NULL. If the xpath expression + for a NOT NULL column does not match anything and there + is no DEFAULT or the DEFAULT also evaluated + to null then the function terminates with an ERROR. + + + + + Unlike normal PostgreSQL functions, the PATH and + DEFAULT arguments to xmltable are not evaluated + to a simple value before calling the function. PATH + expressions are normally evaluated exactly once per input row + , and 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 xmltable + behaves more like a subquery than a function call. This means that you + can usefully use volatile functions like nextval in + DEFAULT expressions, your PATH expressions may + depend on other parts of the XML document, etc. + + + + + A column marked with the + FOR ORDINALITY 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 FOR ORDINALITY. + + + + Only XPath query language is supported. PostgreSQL doesn't support XQuery + language. Then the syntax of xmltable doesn't + allow to use XQuery related functionality - the name of xml expression + (clause AS) is not allowed, and only one xml expression + should be passed to xmltable function as parameter. + + 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 def_elem reloption_elem old_aggr_elem operator_def_elem %type 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 rowsfrom_item rowsfrom_list opt_col_def_list %type opt_ordinality @@ -549,6 +549,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type xmlexists_argument %type document_or_content %type xml_whitespace_option +%type xmltable_column_list xmltable_column_opt_list +%type xmltable_column_el +%type xmltable_column_opt_el +%type xml_namespace_list +%type xml_namespace_el %type func_application func_expr_common_subexpr %type 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 (), (), ... */ 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 '  (1 row) +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES(' + + AU + Australia + 3 + + + CN + China + 3 + + + HK + HongKong + 3 + + + IN + India + 3 + + + JP + Japan + 3Sinzo Abe + + + SG + Singapore + 3791 + +'); +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '1020'); + xmltable +------------- + + + 10+ + 20+ + +(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 '10' + 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 '10' + COLUMNS a int PATH 'zz:a'); +SELECT * FROM xmltableview2; + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'), + '/rows/row' + PASSING '10' + COLUMNS a int PATH 'a'); +ERROR: DEFAULT namespace is not supported +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '1020'); + 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 | + + | | | IN + + | | | India + + | | | 3 + + | | | + 5 | Japan | 3 | + + | | | JP + + | | | Japan + + | | | 3Sinzo Abe+ + | | | +(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 | INIndia3 + 5 | Japan | 3 | JPJapan3Sinzo Abe +(2 rows) + +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text); + element +------------------- + a1aa2a bbbbcccc +(1 row) + +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' 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 ' &"<>!foo]]>2' columns c text); + c +------------------------- + &"<>!foo + 2 +(2 rows) + +-- XML builtin entities +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent text); + ent +----- + ' + " + & + < + > +(5 rows) + +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent xml); + 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(' + + CZ + Czech Republic + 2Milos Zeman + + + DE + Germany + 2 + + + FR + France + 2 + +'); +INSERT INTO xmldata VALUES(' + + EG + Egypt + 1 + + + SD + Sudan + 1 + +'); +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('1', 'A'); +INSERT INTO xmltest2 VALUES('2', 'B'); +INSERT INTO xmltest2 VALUES('3', 'C'); +INSERT INTO xmltest2 VALUES('2', '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 ' + + AU + Australia + 3 + + + CN + China + 3 + + + HK + HongKong + 3 + + + IN + India + 3 + + + JP + Japan + 3Sinzo Abe + + + SG + Singapore + 3791 + +'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmldata VALUES(' + ^ +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 '1020'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('/rows/row' PASSING '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 '10' + COLUMNS a int PATH 'zz:a'); +ERROR: unsupported XML feature +LINE 3: PASSING '10' + COLUMNS a int PATH 'zz:a'); +ERROR: unsupported XML feature +LINE 3: PASSING '10' + COLUMNS a int PATH 'a'); +ERROR: unsupported XML feature +LINE 3: PASSING '1020'); +ERROR: unsupported XML feature +LINE 1: ...LECT * FROM XMLTABLE('/rows/row/a/text()' PASSING 'a1aa2a bbbbxxxcccc' COLUMNS element text); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM xmltable('/root' passing 'a1aa1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail +ERROR: unsupported XML feature +LINE 1: SELECT * FROM xmltable('/root' passing 'a1a &"<>!foo]]>2' columns c text); +ERROR: unsupported XML feature +LINE 1: select * from xmltable('r' passing ''"&<>' COLUMNS ent text); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM xmltable('/x/a' PASSING '''"&<>' COLUMNS ent xml); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM xmltable('/x/a' PASSING '' 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(' + + CZ + Czech Republic + 2Milos Zeman + + + DE + Germany + 2 + + + FR + France + 2 + +'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmldata VALUES(' + ^ +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(' + + EG + Egypt + 1 + + + SD + Sudan + 1 + +'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmldata VALUES(' + ^ +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('1', 'A'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmltest2 VALUES('1', '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('2', 'B'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmltest2 VALUES('2', '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('3', 'C'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmltest2 VALUES('3', '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('2', 'D'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmltest2 VALUES('2', '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 '  (1 row) +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES(' + + AU + Australia + 3 + + + CN + China + 3 + + + HK + HongKong + 3 + + + IN + India + 3 + + + JP + Japan + 3Sinzo Abe + + + SG + Singapore + 3791 + +'); +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '1020'); + xmltable +------------- + + + 10+ + 20+ + +(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 '10' + 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 '10' + COLUMNS a int PATH 'zz:a'); +SELECT * FROM xmltableview2; + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'), + '/rows/row' + PASSING '10' + COLUMNS a int PATH 'a'); +ERROR: DEFAULT namespace is not supported +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '1020'); + 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 | + + | | | IN + + | | | India + + | | | 3 + + | | | + 5 | Japan | 3 | + + | | | JP + + | | | Japan + + | | | 3Sinzo Abe+ + | | | +(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 | INIndia3 + 5 | Japan | 3 | JPJapan3Sinzo Abe +(2 rows) + +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text); + element +------------------- + a1aa2a bbbbcccc +(1 row) + +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' 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 ' &"<>!foo]]>2' columns c text); + c +------------------------- + &"<>!foo + 2 +(2 rows) + +-- XML builtin entities +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent text); + ent +----- + ' + " + & + < + > +(5 rows) + +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent xml); + 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(' + + CZ + Czech Republic + 2Milos Zeman + + + DE + Germany + 2 + + + FR + France + 2 + +'); +INSERT INTO xmldata VALUES(' + + EG + Egypt + 1 + + + SD + Sudan + 1 + +'); +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('1', 'A'); +INSERT INTO xmltest2 VALUES('2', 'B'); +INSERT INTO xmltest2 VALUES('3', 'C'); +INSERT INTO xmltest2 VALUES('2', '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 ']> SELECT XMLPARSE(DOCUMENT ']>&c;'); -- This might or might not load the requested DTD, but it mustn't throw error. SELECT XMLPARSE(DOCUMENT ' '); + +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES(' + + AU + Australia + 3 + + + CN + China + 3 + + + HK + HongKong + 3 + + + IN + India + 3 + + + JP + Japan + 3Sinzo Abe + + + SG + Singapore + 3791 + +'); + +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '1020'); + +-- 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 '10' + COLUMNS a int PATH 'zz:a'); + +CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz), + '/zz:rows/zz:row' + PASSING '10' + COLUMNS a int PATH 'zz:a'); + +SELECT * FROM xmltableview2; + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'), + '/rows/row' + PASSING '10' + COLUMNS a int PATH 'a'); + +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '1020'); + +-- 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 'a1aa2a bbbbxxxcccc' COLUMNS element text); +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail + +-- CDATA test +select * from xmltable('r' passing ' &"<>!foo]]>2' columns c text); + +-- XML builtin entities +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent text); +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' 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(' + + CZ + Czech Republic + 2Milos Zeman + + + DE + Germany + 2 + + + FR + France + 2 + +'); + +INSERT INTO xmldata VALUES(' + + EG + Egypt + 1 + + + SD + Sudan + 1 + +'); + +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('1', 'A'); +INSERT INTO xmltest2 VALUES('2', 'B'); +INSERT INTO xmltest2 VALUES('3', 'C'); +INSERT INTO xmltest2 VALUES('2', 'D'); + +SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c'); +SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.'); +SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54); 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