Hi 2016-08-07 11:15 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
> Hi > > I am sending a initial implementation of xmltable function: > > The code is not clean now, but it does almost of expected work. The usage > is simple. It is fast - 16K entries in 400ms. > > I invite any help with documentation and testing. > > The full ANSI/SQL, or Oracle compatible implementation is not possible due > limits of libxml2, but for typical usage it should to work well. It doesn't > need any new reserved keyword, so there should not be hard barriers for > accepting (when this work will be complete). > > Example: > > postgres=# SELECT * FROM xmldata; > ┌──────────────────────────────────────────────────────────────────┐ > │ data │ > ╞══════════════════════════════════════════════════════════════════╡ > │ <ROWS> ↵│ > │ <ROW id="1"> ↵│ > │ <COUNTRY_ID>AU</COUNTRY_ID> ↵│ > │ <COUNTRY_NAME>Australia</COUNTRY_NAME> ↵│ > │ <REGION_ID>3</REGION_ID> ↵│ > │ </ROW> ↵│ > │ <ROW id="2"> ↵│ > │ <COUNTRY_ID>CN</COUNTRY_ID> ↵│ > │ <COUNTRY_NAME>China</COUNTRY_NAME> ↵│ > │ <REGION_ID>3</REGION_ID> ↵│ > │ </ROW> ↵│ > │ <ROW id="3"> ↵│ > │ <COUNTRY_ID>HK</COUNTRY_ID> ↵│ > │ <COUNTRY_NAME>HongKong</COUNTRY_NAME> ↵│ > │ <REGION_ID>3</REGION_ID> ↵│ > │ </ROW> ↵│ > │ <ROW id="4"> ↵│ > │ <COUNTRY_ID>IN</COUNTRY_ID> ↵│ > │ <COUNTRY_NAME>India</COUNTRY_NAME> ↵│ > │ <REGION_ID>3</REGION_ID> ↵│ > │ </ROW> ↵│ > │ <ROW id="5"> ↵│ > │ <COUNTRY_ID>JP</COUNTRY_ID> ↵│ > │ <COUNTRY_NAME>Japan</COUNTRY_NAME> ↵│ > │ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>↵│ > │ </ROW> ↵│ > │ <ROW id="6"> ↵│ > │ <COUNTRY_ID>SG</COUNTRY_ID> ↵│ > │ <COUNTRY_NAME>Singapore</COUNTRY_NAME> ↵│ > │ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE> ↵│ > │ </ROW> ↵│ > │ </ROWS> │ > └──────────────────────────────────────────────────────────────────┘ > (1 row) > postgres=# SELECT xmltable.* > postgres-# FROM (SELECT data FROM xmldata) x, > postgres-# LATERAL xmltable('/ROWS/ROW' > postgres(# PASSING data > postgres(# COLUMNS id int PATH '@id', > postgres(# country_name text PATH > 'COUNTRY_NAME', > postgres(# country_id text PATH > 'COUNTRY_ID', > postgres(# region_id int PATH > 'REGION_ID', > postgres(# size float PATH 'SIZE', > postgres(# unit text PATH 'SIZE/@unit', > postgres(# premier_name text PATH > 'PREMIER_NAME' DEFAULT 'not specified'); > ┌────┬──────────────┬────────────┬───────────┬──────┬──────┬ > ───────────────┐ > │ id │ country_name │ country_id │ region_id │ size │ unit │ premier_name > │ > ╞════╪══════════════╪════════════╪═══════════╪══════╪══════╪ > ═══════════════╡ > │ 1 │ Australia │ AU │ 3 │ ¤ │ ¤ │ not specified > │ > │ 2 │ China │ CN │ 3 │ ¤ │ ¤ │ not specified > │ > │ 3 │ HongKong │ HK │ 3 │ ¤ │ ¤ │ not specified > │ > │ 4 │ India │ IN │ 3 │ ¤ │ ¤ │ not specified > │ > │ 5 │ Japan │ JP │ 3 │ ¤ │ ¤ │ Sinzo Abe > │ > │ 6 │ Singapore │ SG │ 3 │ 791 │ km │ not specified > │ > └────┴──────────────┴────────────┴───────────┴──────┴──────┴ > ───────────────┘ > (6 rows) > > Regards > > I am sending updated version - the code is not better, but there is full functionality implemented. * xmlnamespaces, * default xmlnamespace, * ordinality column, * NOT NULL constraint, * mode without explicitly defined columns. Lot of bugs was fixed - it is ready for some playing. tests, comments, notes, comparing with other db are welcome. Some behave is based by libxml2 possibilities - so only XPath is supported. Regards Pavel > Pavel >
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index 69bf65d..a20c576 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -58,7 +58,6 @@ #include "utils/typcache.h" #include "utils/xml.h" - /* static function decls */ static Datum ExecEvalArrayRef(ArrayRefExprState *astate, ExprContext *econtext, @@ -149,6 +148,8 @@ static Datum ExecEvalMinMax(MinMaxExprState *minmaxExpr, bool *isNull, ExprDoneCond *isDone); static Datum ExecEvalXml(XmlExprState *xmlExpr, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); +static Datum ExecEvalXmlTable(XmlTableState *xmltable, ExprContext *econtext, + bool *isnull, ExprDoneCond *isDone); static Datum ExecEvalNullIf(FuncExprState *nullIfExpr, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); @@ -2073,6 +2074,7 @@ ExecMakeTableFunctionResult(ExprState *funcexpr, MemoryContext oldcontext; bool direct_function_call; bool first_time = true; + bool xmltable_call; callerContext = CurrentMemoryContext; @@ -2118,6 +2120,7 @@ ExecMakeTableFunctionResult(ExprState *funcexpr, * This path is similar to ExecMakeFunctionResult. */ direct_function_call = true; + xmltable_call = false; /* * Initialize function cache if first time through @@ -2172,10 +2175,16 @@ ExecMakeTableFunctionResult(ExprState *funcexpr, } } } + else if (funcexpr && IsA(funcexpr, XmlTableState)) + { + direct_function_call = false; + xmltable_call = true; + } else { /* Treat funcexpr as a generic expression */ direct_function_call = false; + xmltable_call = false; InitFunctionCallInfoData(fcinfo, NULL, 0, InvalidOid, NULL, NULL); } @@ -2213,6 +2222,14 @@ ExecMakeTableFunctionResult(ExprState *funcexpr, pgstat_end_function_usage(&fcusage, rsinfo.isDone != ExprMultipleResult); } + else if (xmltable_call) + { + XmlTableState *xmltable = (XmlTableState *) funcexpr; + + xmltable->rsinfo = &rsinfo; + result = ExecEvalExpr(funcexpr, econtext, + &fcinfo.isnull, &rsinfo.isDone); + } else { result = ExecEvalExpr(funcexpr, econtext, @@ -3737,6 +3754,338 @@ ExecEvalXml(XmlExprState *xmlExpr, ExprContext *econtext, return (Datum) 0; } +/************************************************* + * Note: + * + * libxml2 doesn't support default namespace in XPath + * expressions, so we can inject (but it needs XPath parser + * or we should not to support it. + ************************************************* + */ + +/* + * This mechanism is too simple - for real usage the real + * XPATH expression is necessary. + */ +static char * +inject_default_ns(char *xpath_expr) +{ + StringInfo str = makeStringInfo(); + char *buffer; + char *bufptr; + char *xpath_expr_ptr; + + buffer = palloc(strlen(xpath_expr)); + bufptr = buffer; + xpath_expr_ptr = xpath_expr; + + *bufptr = '\0'; + + while (*xpath_expr_ptr != '\0') + { + if (*xpath_expr_ptr == '/') + { + if (*buffer != '\0') + { + if (strchr(buffer, '(') != NULL || strchr(buffer, '.') != NULL || + strchr(buffer, '@') != NULL || strchr(buffer, ':') != NULL ) + { + appendStringInfoString(str, buffer); + } + else + { + appendStringInfoString(str, "pgdefxmlns:"); + appendStringInfoString(str, buffer); + } + } + bufptr = buffer; + *bufptr = '\0'; + appendStringInfoChar(str, '/'); + xpath_expr_ptr++; + } + else + { + *bufptr++ = *xpath_expr_ptr++; + *bufptr = '\0'; + } + } + if (*buffer != '\0') + { + if (strchr(buffer, '(') != NULL || strchr(buffer, '.') != NULL || strchr(buffer, '@') != NULL || strchr(buffer, ':') != NULL ) + { + appendStringInfoString(str, buffer); + } + else + { + appendStringInfoString(str, "pgdefxmlns:"); + appendStringInfoString(str, buffer); + } + } + + pfree(buffer); + + return str->data; +} + +static Datum +ExecEvalXmlTable(XmlTableState *xmltable, + ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone) +{ + Datum *values; + bool *nulls; + int i; + int ncols = xmltable->ncolumns; + ReturnSetInfo *rsinfo = xmltable->rsinfo; + Tuplestorestate *tupstore; + MemoryContext per_query_ctx; + MemoryContext oldctx; + TupleDesc tupdesc; + Datum value; + bool isnull; + char **paths; + PgXmlTableContext *xmltableCxt; + text *xpath_expr_text; + xmltype *data; + FmgrInfo *in_functions; + Oid *typioparams; + ListCell *l; + int64 rc = 1; + int nnamespaces; + char **namespaces_uri; + char **namespaces_name; + bool use_default_ns = false; + + /* check valid usage */ + if (xmltable->rsinfo == NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("xmltable function is called in unsupported expression context"))); + + tupdesc = xmltable->rsinfo->expectedDesc; + + /* first execution, prepare in_function array */ + if (xmltable->in_functions == NULL) + { + Oid in_func_oid; + Form_pg_attribute *attr; + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldctx = MemoryContextSwitchTo(per_query_ctx); + + attr = tupdesc->attrs; + Assert(tupdesc->natts == ncols); + + in_functions = xmltable->in_functions = palloc(sizeof(FmgrInfo) * ncols); + typioparams = xmltable->typioparams = palloc(sizeof(Oid) * ncols); + for(i = 0; i < ncols; i++) + { + getTypeInputInfo(attr[i]->atttypid, + &in_func_oid, &typioparams[i]); + fmgr_info(in_func_oid, &in_functions[i]); + } + + MemoryContextSwitchTo(oldctx); + } + else + { + in_functions = xmltable->in_functions; + typioparams = xmltable->typioparams; + } + + nnamespaces = list_length(xmltable->namespaces); + if (nnamespaces > 0) + { + namespaces_uri = palloc0(sizeof(char *) * nnamespaces); + namespaces_name = palloc0(sizeof(char *) * nnamespaces); + i = 0; + + foreach(l, xmltable->namespaces) + { + ResTarget *rt = (ResTarget *) lfirst(l); + + value = ExecEvalExpr((ExprState *) rt->val, econtext, &isnull, NULL); + if (isnull) + elog(ERROR, "NULL is not allowed in xml namespaces specification"); + + namespaces_uri[i] = text_to_cstring(DatumGetTextP(value)); + namespaces_name[i] = rt->name; + if (rt->name != NULL) + { + int j; + for (j = 0; j < i; j++) + if (namespaces_name[j] != NULL && strcmp(namespaces_name[j], rt->name) == 0) + elog(ERROR, "namespaces name are not unique \"%s\"", rt->name); + } + else + { + namespaces_name[i] = "pgdefxmlns"; + use_default_ns = true; + } + i++; + } + } + else + { + namespaces_uri = NULL; + namespaces_name = NULL; + } + + paths = palloc(sizeof(char *) * ncols); + for (i = 0; i < ncols; i++) + { + char *curr_path; + bool use_prefix = false; + bool use_suffix = true; + int j; + char *last_char_ptr; + + if (xmltable->path_exprs[i] != NULL) + { + value = ExecEvalExpr(xmltable->path_exprs[i], econtext, &isnull, NULL); + + if (isnull) + elog(ERROR, "expression of PATH of column %s is NULL", + NameStr(tupdesc->attrs[i]->attname)); + paths[i] = text_to_cstring(DatumGetTextP(value)); + } + else + paths[i] = NameStr(tupdesc->attrs[i]->attname); + + /* When doesn't start with any special cha, we should to append ref to parent node */ + curr_path = paths[i]; + if (curr_path[0] != '.' && curr_path[0] != '/') + use_prefix = true; + + last_char_ptr = curr_path + strlen(curr_path) - 1; + if (*last_char_ptr == ')' || *last_char_ptr == '/') + use_suffix = false; + + /* path shoud be reference to attribute */ + if (use_suffix) + { + for (j = strlen(curr_path); j > 0; j--) + { + if (*last_char_ptr == '/') + break; + if (*last_char_ptr-- == '@') + { + use_suffix = false; + break; + } + } + } + + /* don't use suffix when target type is a XML */ + if (use_suffix && tupdesc->attrs[i]->atttypid == XMLOID) + use_suffix = false; + + if (use_prefix || use_suffix) + { + char *buffer = palloc(strlen(curr_path) + 1 + 2 + 7); + + sprintf(buffer, "%s%s%s", use_prefix ? "./" : "", curr_path, use_suffix ? "/text()" : ""); + if (!use_default_ns) + paths[i] = buffer; + else + paths[i] = inject_default_ns(buffer); + } + } + + value = ExecEvalExpr(xmltable->query_string, econtext, &isnull, NULL); + if (isnull) + elog(ERROR, "query string should not be NULL"); + xpath_expr_text = DatumGetTextP(value); + + if (use_default_ns) + xpath_expr_text = cstring_to_text(inject_default_ns(text_to_cstring(xpath_expr_text))); + + value = ExecEvalExpr(xmltable->expr, econtext, &isnull, NULL); + if (isnull) + elog(ERROR, "input value should not be NULL (ToDo: should be fixed in future)"); + data = DatumGetXmlP(value); + + values = palloc0(sizeof(Datum) * ncols); + nulls = palloc0(sizeof(bool) * ncols); + + xmltableCxt = makeXmlTableCxt(xpath_expr_text, data, ncols, paths, + in_functions, typioparams, tupdesc, + nnamespaces, namespaces_uri, namespaces_name, + xmltable->with_auto_col); + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldctx = MemoryContextSwitchTo(per_query_ctx); + tupstore = tuplestore_begin_heap(false, false, work_mem); + + PG_TRY(); + { + for(;;) + { + MemoryContextSwitchTo(oldctx); + + if (XmlTableGetRow(xmltableCxt)) + { + bool isnull; + Datum value; + HeapTuple tuple; + + for (i = 0; i < ncols; i++) + { + value = XmlTableGetValue(xmltableCxt, i, &isnull); + + if (isnull && xmltable->default_exprs[i] != NULL) + value = ExecEvalExpr(xmltable->default_exprs[i], econtext, &isnull, NULL); + + if (isnull) + { + + nulls[i] = true; + if (xmltable->is_not_nulls[i]) + elog(ERROR, "value is null"); + } + else + { + values[i] = value; + nulls[i] = false; + } + + } + if (xmltable->ordinality_colnum > 0) + { + values[xmltable->ordinality_colnum - 1] = Int8GetDatum(rc++); + nulls[xmltable->ordinality_colnum - 1] = false; + } + + tuple = heap_form_tuple(tupdesc, values, nulls); + + MemoryContextSwitchTo(per_query_ctx); + tuplestore_puttuple(tupstore, tuple); + } + else + break; + } + + MemoryContextSwitchTo(per_query_ctx); + tuplestore_donestoring(tupstore); + + XmlTableCxtFree(xmltableCxt); + } + PG_CATCH(); + { + XmlTableCxtFree(xmltableCxt); + + PG_RE_THROW(); + } + PG_END_TRY(); + + *isDone = ExprSingleResult; + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = CreateTupleDescCopy(tupdesc); /* this destroy passed tupdesc, use copy! */ + + return (Datum) 0; +} + /* ---------------------------------------------------------------- * ExecEvalNullIf * @@ -5107,6 +5456,57 @@ ExecInitExpr(Expr *node, PlanState *parent) state = (ExprState *) xstate; } break; + case T_XmlTable: + { + XmlTable *xt = (XmlTable *) node; + XmlTableState *xstate = makeNode(XmlTableState); + ListCell *l; + int i = 0; + int ncols; + + xstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalXmlTable; + xstate->query_string = ExecInitExpr((Expr *) xt->query_string, parent); + xstate->expr = ExecInitExpr((Expr *) xt->expr, parent); + + ncols = list_length(xt->coldeflist); + xstate->ncolumns = ncols; + xstate->path_exprs = palloc0(sizeof(ExprState *) * ncols); + xstate->default_exprs = palloc0(sizeof(ExprState *) * ncols); + xstate->with_auto_col = xt->with_auto_col; + + xstate->is_not_nulls = palloc0(sizeof(bool) * ncols); + + foreach(l, xt->namespaces) + { + ResTarget *res = (ResTarget *) lfirst(l); + ResTarget *newres = makeNode(ResTarget); + + newres->name = res->name; + newres->val = (Node *) ExecInitExpr((Expr *) res->val, parent); + newres->location = res->location; + + xstate->namespaces = lappend(xstate->namespaces, newres); + } + + foreach(l, xt->coldeflist) + { + ColumnDef *def = (ColumnDef *) lfirst(l); + + if (def->cooked_path) + xstate->path_exprs[i] = ExecInitExpr((Expr *) def->cooked_path, parent); + if (def->cooked_default) + xstate->default_exprs[i] = ExecInitExpr((Expr *) def->cooked_default, parent); + + if (def->for_ordinality) + xstate->ordinality_colnum = i + 1; + if (def->is_not_null) + xstate->is_not_nulls[i] = true; + i++; + } + + state = (ExprState *) xstate; + } + break; case T_NullTest: { NullTest *ntest = (NullTest *) node; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 3244c76..7b4b422 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2553,6 +2553,7 @@ _copyRangeTableSample(const RangeTableSample *from) return newnode; } + static TypeCast * _copyTypeCast(const TypeCast *from) { @@ -2607,6 +2608,8 @@ _copyColumnDef(const ColumnDef *from) COPY_SCALAR_FIELD(storage); COPY_NODE_FIELD(raw_default); COPY_NODE_FIELD(cooked_default); + COPY_NODE_FIELD(raw_path); + COPY_NODE_FIELD(cooked_path); COPY_NODE_FIELD(collClause); COPY_SCALAR_FIELD(collOid); COPY_NODE_FIELD(constraints); @@ -2688,6 +2691,20 @@ _copyXmlSerialize(const XmlSerialize *from) return newnode; } +static XmlTable * +_copyXmlTable(const XmlTable *from) +{ + XmlTable *newnode = makeNode(XmlTable); + + COPY_NODE_FIELD(namespaces); + COPY_NODE_FIELD(query_string); + COPY_NODE_FIELD(expr); + COPY_NODE_FIELD(coldeflist); + COPY_LOCATION_FIELD(location); + + return newnode; +} + static RoleSpec * _copyRoleSpec(const RoleSpec *from) { @@ -5062,6 +5079,9 @@ copyObject(const void *from) case T_XmlSerialize: retval = _copyXmlSerialize(from); break; + case T_XmlTable: + retval = _copyXmlTable(from); + break; case T_RoleSpec: retval = _copyRoleSpec(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 1eb6799..259d9e1 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2339,6 +2339,7 @@ _equalRangeTableSample(const RangeTableSample *a, const RangeTableSample *b) return true; } + static bool _equalIndexElem(const IndexElem *a, const IndexElem *b) { @@ -2365,6 +2366,8 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef *b) COMPARE_SCALAR_FIELD(storage); COMPARE_NODE_FIELD(raw_default); COMPARE_NODE_FIELD(cooked_default); + COMPARE_NODE_FIELD(raw_path); + COMPARE_NODE_FIELD(cooked_path); COMPARE_NODE_FIELD(collClause); COMPARE_SCALAR_FIELD(collOid); COMPARE_NODE_FIELD(constraints); @@ -2610,6 +2613,18 @@ _equalXmlSerialize(const XmlSerialize *a, const XmlSerialize *b) } static bool +_equalXmlTable(const XmlTable *a, const XmlTable *b) +{ + COMPARE_NODE_FIELD(namespaces); + COMPARE_NODE_FIELD(query_string); + COMPARE_NODE_FIELD(expr); + COMPARE_NODE_FIELD(coldeflist); + COMPARE_LOCATION_FIELD(location); + + return true; +} + +static bool _equalRoleSpec(const RoleSpec *a, const RoleSpec *b) { COMPARE_SCALAR_FIELD(roletype); @@ -3366,6 +3381,9 @@ equal(const void *a, const void *b) case T_XmlSerialize: retval = _equalXmlSerialize(a, b); break; + case T_XmlTable: + retval = _equalXmlTable(a, b); + break; case T_RoleSpec: retval = _equalRoleSpec(a, b); break; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index cd39167..86b2c66 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -226,6 +226,9 @@ exprType(const Node *expr) else type = XMLOID; break; + case T_XmlTable: + type = RECORDOID; + break; case T_NullTest: type = BOOLOID; break; @@ -720,6 +723,8 @@ expression_returns_set_walker(Node *node, void *context) return false; if (IsA(node, XmlExpr)) return false; + if (IsA(node, XmlTable)) + return true; return expression_tree_walker(node, expression_returns_set_walker, context); @@ -895,6 +900,12 @@ exprCollation(const Node *expr) else coll = InvalidOid; break; + /* + * result is composite or XML + */ + case T_XmlTable: + coll = InvalidOid; + break; case T_NullTest: coll = InvalidOid; /* result is always boolean */ break; @@ -1096,6 +1107,9 @@ exprSetCollation(Node *expr, Oid collation) (collation == DEFAULT_COLLATION_OID) : (collation == InvalidOid)); break; + case T_XmlTable: + Assert(!OidIsValid(collation)); /* result is always XML */ + break; case T_NullTest: Assert(!OidIsValid(collation)); /* result is always boolean */ break; @@ -1512,6 +1526,9 @@ exprLocation(const Node *expr) /* XMLSERIALIZE keyword should always be the first thing */ loc = ((const XmlSerialize *) expr)->location; break; + case T_XmlTable: + loc = ((const XmlTable *) expr)->location; + break; case T_GroupingSet: loc = ((const GroupingSet *) expr)->location; break; @@ -2067,6 +2084,35 @@ expression_tree_walker(Node *node, return true; } break; + case T_XmlTable: + { + XmlTable *xt = (XmlTable *) node; + ListCell *l; + + if (walker(xt->query_string, context)) + return true; + if (walker(xt->expr, context)) + return true; + + foreach(l, xt->namespaces) + { + ResTarget *rt = (ResTarget *) lfirst(l); + + if (walker(rt->val, context)) + return true; + } + + foreach(l, xt->coldeflist) + { + ColumnDef *def = (ColumnDef *) lfirst(l); + + if (walker(def->cooked_default, context)) + return true; + if (walker(def->cooked_path, context)) + return true; + } + } + break; case T_NullTest: return walker(((NullTest *) node)->arg, context); case T_BooleanTest: @@ -2781,6 +2827,45 @@ expression_tree_mutator(Node *node, return (Node *) newnode; } break; + case T_XmlTable: + { + XmlTable *xt = (XmlTable *) node; + XmlTable *newnode; + List *resultlist = NIL; + ListCell *l; + + FLATCOPY(newnode, xt, XmlTable); + MUTATE(newnode->query_string, xt->query_string, Node *); + MUTATE(newnode->expr, xt->expr, Node *); + + resultlist = NIL; + foreach(l, xt->namespaces) + { + ResTarget *rt = (ResTarget *) lfirst(l); + ResTarget *newrt; + + FLATCOPY(newrt, rt, ResTarget); + MUTATE(newrt->val, rt->val, Node *); + resultlist = lappend(resultlist, newrt); + } + newnode->namespaces = resultlist; + + resultlist = NIL; + foreach(l, xt->coldeflist) + { + ColumnDef *def = (ColumnDef *) lfirst(l); + ColumnDef *newdef; + + FLATCOPY(newdef, def, ColumnDef); + MUTATE(newdef->cooked_default, def->cooked_default, Node *); + MUTATE(newdef->cooked_path, def->cooked_path, Node *); + resultlist = lappend(resultlist, newdef); + } + newnode->coldeflist = resultlist; + + return (Node *) newnode; + } + break; case T_NullTest: { NullTest *ntest = (NullTest *) node; @@ -3547,6 +3632,8 @@ raw_expression_tree_walker(Node *node, return true; if (walker(coldef->raw_default, context)) return true; + if (walker(coldef->raw_path, context)) + return true; if (walker(coldef->collClause, context)) return true; /* for now, constraints are ignored */ @@ -3575,6 +3662,20 @@ raw_expression_tree_walker(Node *node, return true; } break; + case T_XmlTable: + { + XmlTable *xt = (XmlTable *) node; + + if (walker(xt->namespaces, context)) + return true; + if (walker(xt->query_string, context)) + return true; + if (walker(xt->expr, context)) + return true; + if (walker(xt->coldeflist, context)) + return true; + } + break; case T_WithClause: return walker(((WithClause *) node)->ctes, context); case T_InferClause: diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index acaf4ea..9e10c88 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2562,6 +2562,18 @@ _outXmlSerialize(StringInfo str, const XmlSerialize *node) } static void +_outXmlTable(StringInfo str, const XmlTable *node) +{ + WRITE_NODE_TYPE("XMLTABLE"); + + WRITE_NODE_FIELD(namespaces); + WRITE_NODE_FIELD(query_string); + WRITE_NODE_FIELD(expr); + WRITE_NODE_FIELD(coldeflist); + WRITE_LOCATION_FIELD(location); +} + +static void _outColumnDef(StringInfo str, const ColumnDef *node) { WRITE_NODE_TYPE("COLUMNDEF"); @@ -2575,6 +2587,8 @@ _outColumnDef(StringInfo str, const ColumnDef *node) WRITE_CHAR_FIELD(storage); WRITE_NODE_FIELD(raw_default); WRITE_NODE_FIELD(cooked_default); + WRITE_NODE_FIELD(raw_path); + WRITE_NODE_FIELD(cooked_path); WRITE_NODE_FIELD(collClause); WRITE_OID_FIELD(collOid); WRITE_NODE_FIELD(constraints); @@ -3845,6 +3859,9 @@ outNode(StringInfo str, const void *obj) case T_XmlSerialize: _outXmlSerialize(str, obj); break; + case T_XmlTable: + _outXmlTable(str, obj); + break; case T_ForeignKeyCacheInfo: _outForeignKeyCacheInfo(str, obj); break; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index a69af7c..6ead0b7 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -806,6 +806,11 @@ expression_returns_set_rows_walker(Node *node, double *count) if (expr->funcretset) *count *= get_func_rows(expr->funcid); } + if (IsA(node, XmlTable)) + { + *count = 100; + return false; + } if (IsA(node, OpExpr)) { OpExpr *expr = (OpExpr *) node; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0cae446..f672f4c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -132,7 +132,7 @@ typedef struct ImportQual #define parser_yyerror(msg) scanner_yyerror(msg, yyscanner) #define parser_errposition(pos) scanner_errposition(pos, yyscanner) -static void base_yyerror(YYLTYPE *yylloc, core_yyscan_t yyscanner, +static void base_yyerror(YYLTYPE *yylloc, core_yyscan_t yyscanner, const char *msg); static Node *makeColumnRef(char *colname, List *indirection, int location, core_yyscan_t yyscanner); @@ -227,6 +227,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); struct ImportQual *importqual; InsertStmt *istmt; VariableSetStmt *vsetstmt; + XmlTableColOpt *xmltablecolopt; } %type <node> stmt schema_stmt @@ -372,6 +373,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); create_generic_options alter_generic_options relation_expr_list dostmt_opt_list transform_element_list transform_type_list + XmltableColumnList %type <list> group_by_list %type <node> group_by_item empty_grouping_set rollup_clause cube_clause @@ -540,6 +542,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <str> opt_existing_window_name %type <boolean> opt_if_not_exists +%type <node> xmltable_column +%type <target> xmlnamespace_el +%type <xmltablecolopt> xmltable_column_option +%type <list> opt_xmltable_column_option_list xmltable_column_option_list +%type <list> xmlnamespace_list +%type <boolean> disallow_null + /* * Non-keyword token types. These are hard-wired into the "flex" lexer. * They must be listed first so that their numeric codes do not depend on @@ -571,7 +580,7 @@ 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 + 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 @@ -615,7 +624,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER - PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY + PARALLEL PARSER PARTIAL PARTITION PASSING PATH PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROGRAM @@ -644,8 +653,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 @@ -12617,6 +12626,44 @@ func_expr_common_subexpr: n->location = @1; $$ = (Node *)n; } + | XMLTABLE '(' c_expr xmlexists_argument ')' + { + XmlTable *n = makeNode(XmlTable); + n->query_string = $3; + n->expr = $4; + n->coldeflist = NIL; + n->location = @1; + $$ = (Node *)n; + } + | XMLTABLE '(' XMLNAMESPACES '(' xmlnamespace_list ')' c_expr xmlexists_argument ')' + { + XmlTable *n = makeNode(XmlTable); + n->namespaces = $5; + n->query_string = $7; + n->expr = $8; + n->coldeflist = NIL; + n->location = @1; + $$ = (Node *)n; + } + | XMLTABLE '(' c_expr xmlexists_argument COLUMNS XmltableColumnList ')' + { + XmlTable *n = makeNode(XmlTable); + n->query_string = $3; + n->expr = $4; + n->coldeflist = $6; + n->location = @1; + $$ = (Node *)n; + } + | XMLTABLE '(' XMLNAMESPACES '(' xmlnamespace_list ')' c_expr xmlexists_argument COLUMNS XmltableColumnList ')' + { + XmlTable *n = makeNode(XmlTable); + n->namespaces = $5; + n->query_string = $7; + n->expr = $8; + n->coldeflist = $10; + n->location = @1; + $$ = (Node *)n; + } ; /* @@ -12663,6 +12710,28 @@ xml_attribute_el: a_expr AS ColLabel } ; +xmlnamespace_list: xmlnamespace_el { $$ = list_make1($1); } + | xmlnamespace_list ',' xmlnamespace_el { $$ = lappend($1, $3); } + ; + +xmlnamespace_el: a_expr AS ColLabel + { + $$ = makeNode(ResTarget); + $$->name = $3; + $$->indirection = NIL; + $$->val = (Node *) $1; + $$->location = @1; + } + | DEFAULT a_expr + { + $$ = makeNode(ResTarget); + $$->name = NULL; + $$->indirection = NIL; + $$->val = (Node *) $2; + $$->location = @1; + } + ; + document_or_content: DOCUMENT_P { $$ = XMLOPTION_DOCUMENT; } | CONTENT_P { $$ = XMLOPTION_CONTENT; } ; @@ -12692,6 +12761,108 @@ xmlexists_argument: } ; +xmltable_column_option: + DEFAULT c_expr + { + $$ = palloc(sizeof(XmlTableColOpt)); + + $$->typ = XMLTABLE_COLUMN_OPT_DEFAULT; + $$->expr = $2; + } + | PATH c_expr + { + $$ = palloc(sizeof(XmlTableColOpt)); + + $$->typ = XMLTABLE_COLUMN_OPT_PATH; + $$->expr = $2; + } + ; + +xmltable_column_option_list: + xmltable_column_option { $$ = list_make1($1); } + | xmltable_column_option_list xmltable_column_option { $$ = lappend($1, $2); } + ; + +opt_xmltable_column_option_list: + xmltable_column_option_list { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } + ; + +xmltable_column: + ColId Typename opt_xmltable_column_option_list disallow_null + { + ListCell *l; + ColumnDef *c = makeNode(ColumnDef); + bool path_is_def = false; + bool default_is_def = false; + + c->colname = $1; + c->typeName = $2; + c->raw_default = NULL; + c->raw_path = NULL; + c->storage = 55; + c->is_not_null = $4; + + foreach(l, $3) + { + XmlTableColOpt *opt = (XmlTableColOpt *) lfirst(l); + + if (opt->typ == XMLTABLE_COLUMN_OPT_PATH) + { + if (path_is_def) + elog(ERROR, "duplicate PATH"); + else + path_is_def = true; + + c->raw_path = opt->expr; + } + else if (opt->typ == XMLTABLE_COLUMN_OPT_DEFAULT) + { + if (default_is_def) + elog(ERROR, "duplicit DEFAULT"); + else + default_is_def = true; + + c->raw_default = opt->expr; + } + else + elog(ERROR, "unknown xmltable column option"); + } + + c->location = @1; + $$ = (Node *) c; + } + | ColId FOR ORDINALITY + { + ColumnDef *c = makeNode(ColumnDef); + + c->colname = $1; + c->for_ordinality = true; + c->location = @1; + $$ = (Node *) c; + } + ; + +disallow_null: + NOT NULL_P + { + $$ = true; + } + | NULL_P + { + $$ = false; + } + | /* EMPTY */ + { + $$ = false; + } + ; + +XmltableColumnList: + xmltable_column { $$ = list_make1($1); } + | XmltableColumnList ',' xmltable_column { $$ = lappend($1, $3); } + ; + /* * Aggregate decoration clauses @@ -13755,6 +13926,7 @@ unreserved_keyword: | CLASS | CLOSE | CLUSTER + | COLUMNS | COMMENT | COMMENTS | COMMIT @@ -13888,6 +14060,7 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PATH | PLANS | POLICY | PRECEDING @@ -14053,10 +14226,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 751de4b..b409b93 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -113,7 +113,7 @@ transformFromClause(ParseState *pstate, List *frmList) /* * The grammar will have produced a list of RangeVars, RangeSubselects, - * RangeFunctions, and/or JoinExprs. Transform each one (possibly adding + * RangeFunctions and/or JoinExprs. Transform each one (possibly adding * entries to the rtable), check for duplicate refnames, and then add it * to the joinlist and namespace. * @@ -821,7 +821,6 @@ transformRangeTableSample(ParseState *pstate, RangeTableSample *rts) return tablesample; } - /* * transformFromClauseItem - * Transform a FROM-clause item, adding any required entries to the diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index cead212..9af33cb 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -99,6 +99,7 @@ static Node *transformAExprIn(ParseState *pstate, A_Expr *a); static Node *transformAExprBetween(ParseState *pstate, A_Expr *a); static Node *transformBoolExpr(ParseState *pstate, BoolExpr *a); static Node *transformFuncCall(ParseState *pstate, FuncCall *fn); +static Node *transformXmlTable(ParseState *pstate, XmlTable *xt); static Node *transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref); static Node *transformCaseExpr(ParseState *pstate, CaseExpr *c); static Node *transformSubLink(ParseState *pstate, SubLink *sublink); @@ -314,6 +315,10 @@ transformExprRecurse(ParseState *pstate, Node *expr) result = transformXmlSerialize(pstate, (XmlSerialize *) expr); break; + case T_XmlTable: + result = transformXmlTable(pstate, (XmlTable *) expr); + break; + case T_NullTest: { NullTest *n = (NullTest *) expr; @@ -1474,6 +1479,94 @@ transformFuncCall(ParseState *pstate, FuncCall *fn) } static Node * +transformXmlTable(ParseState *pstate, XmlTable *xt) +{ + ListCell *l; + XmlTable *newxt = makeNode(XmlTable); + bool found_default_ns = false; + + newxt->query_string = coerce_to_specific_type(pstate, + transformExprRecurse(pstate, xt->query_string), + TEXTOID, + "XMLTABLE"); + newxt->expr = coerce_to_specific_type(pstate, + transformExprRecurse(pstate, xt->expr), + XMLOID, + "XMLTABLE"); + + newxt->location = xt->location; + + foreach(l, xt->namespaces) + { + ResTarget *res = (ResTarget *) lfirst(l); + ResTarget *newres; + + newres = makeNode(ResTarget); + + if (res->name == NULL) + { + if (found_default_ns) + elog(ERROR, "only one default namespace is allowed"); + found_default_ns = true; + } + + newres->name = res->name; + newres->val = coerce_to_specific_type(pstate, + transformExprRecurse(pstate, res->val), + TEXTOID, + "XMLTABLE"); + newxt->namespaces = lappend(newxt->namespaces, newres); + newres->location = res->location; + } + + /* Append default column, where none is defined */ + if (xt->coldeflist == NIL) + { + ColumnDef *col = makeNode(ColumnDef); + + col->colname = "xmltable"; /* ToDo: what name should be used? Using pg convention now */ + col->typeName = makeTypeNameFromOid(XMLOID, -1); + col->location = -1; + col->cooked_path = (Node *) makeConst(TEXTOID, -1, -1, -1, PointerGetDatum(cstring_to_text(".")), false, false); + + xt->coldeflist = list_make1(col); + newxt->with_auto_col = true; + } + + foreach(l, xt->coldeflist) + { + ColumnDef *def = (ColumnDef *) lfirst(l); + + if (def->raw_default) + { + Oid targetType = typenameTypeId(pstate, def->typeName); + + def->cooked_default = coerce_to_specific_type(pstate, + transformExprRecurse(pstate, def->raw_default), + targetType, + "XMLTABLE"); + } + + if (def->raw_path) + def->cooked_path = coerce_to_specific_type(pstate, + transformExprRecurse(pstate, def->raw_path), + TEXTOID, + "XMLTABLE"); + + if (def->for_ordinality) + { + if (newxt->with_ordinality) + elog(ERROR, "only one FOR ORDINALITY column is allowd"); + newxt->with_ordinality = true; + } + } + + newxt->coldeflist = xt->coldeflist; + + return (Node *) newxt; +} + +static Node * transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref) { SubLink *sublink; @@ -2259,7 +2352,7 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x) i = 0; foreach(lc, x->args) { - Node *e = (Node *) lfirst(lc); + Node *e = (Node *) lfirst(lc); Node *newe; newe = transformExprRecurse(pstate, e); diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index fc93063..a4de207 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1794,6 +1794,9 @@ FigureColnameInternal(Node *node, char **name) case T_XmlSerialize: *name = "xmlserialize"; return 2; + case T_XmlTable: + *name = "xmltable"; + return 2; default: break; } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d68ca7a..e6dd75e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8022,6 +8022,38 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_XmlTable: + { + XmlTable *xt = (XmlTable *) node; + + appendStringInfoString(buf, "XMLTABLE("); + get_rule_expr((Node *) xt->query_string, context, true); + appendStringInfoString(buf, " PASSING "); + get_rule_expr((Node *) xt->expr, context, true); + + if (xt->coldeflist != NIL) + { + ListCell *l; + bool isFirst = true; + + appendStringInfoString(buf, " COLUMNS "); + foreach(l, xt->coldeflist) + { + ColumnDef *def = (ColumnDef *) lfirst(l); + + if (!isFirst) + appendStringInfoString(buf, ", "); + else + isFirst = false; + appendStringInfo(buf, "%s", def->colname); + } + appendStringInfoChar(buf, ' '); + } + + appendStringInfoChar(buf, ')'); + } + break; + case T_NullTest: { NullTest *ntest = (NullTest *) node; diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c index 7ed5bcb..ccad864 100644 --- a/src/backend/utils/adt/xml.c +++ b/src/backend/utils/adt/xml.c @@ -115,6 +115,28 @@ struct PgXmlErrorContext xmlExternalEntityLoader saved_entityfunc; }; +struct PgXmlTableContext +{ + int ncols; + char **paths; + TupleDesc tupdesc; + + PgXmlErrorContext *xmlerrcxt; + volatile xmlParserCtxtPtr ctxt; + volatile xmlDocPtr doc; + volatile xmlXPathContextPtr xpathctx; + volatile xmlXPathCompExprPtr xpathcomp; + volatile xmlXPathObjectPtr xpathobj; + + volatile xmlXPathCompExprPtr *xpathscomp; + + FmgrInfo *in_functions; + Oid *typioparams; + + long int rc; + bool skip_second_search; +}; + static xmlParserInputPtr xmlPgEntityLoader(const char *URL, const char *ID, xmlParserCtxtPtr ctxt); static void xml_errorHandler(void *data, xmlErrorPtr error); @@ -142,6 +164,7 @@ static bool print_xml_decl(StringInfo buf, const xmlChar *version, static xmlDocPtr xml_parse(text *data, XmlOptionType xmloption_arg, bool preserve_whitespace, int encoding); static text *xml_xmlnodetoxmltype(xmlNodePtr cur, PgXmlErrorContext *xmlerrcxt); +static char *xml_xmlnodetostr(xmlNodePtr cur, PgXmlErrorContext *xmlerrcxt); static int xml_xpathobjtoxmlarray(xmlXPathObjectPtr xpathobj, ArrayBuildState *astate, PgXmlErrorContext *xmlerrcxt); @@ -4073,3 +4096,398 @@ xml_is_well_formed_content(PG_FUNCTION_ARGS) return 0; #endif /* not USE_LIBXML */ } + +/* + * Convert XML node to cstring (dump subtree in case of element, + * return value otherwise) + */ +#ifdef USE_LIBXML + +static char * +xml_xmlnodetostr(xmlNodePtr cur, PgXmlErrorContext *xmlerrcxt) +{ + char *result; + + if (cur->type == XML_ELEMENT_NODE) + { + xmlBufferPtr buf; + xmlNodePtr cur_copy; + + buf = xmlBufferCreate(); + + /* + * The result of xmlNodeDump() won't contain namespace definitions + * from parent nodes, but xmlCopyNode() duplicates a node along with + * its required namespace definitions. + */ + cur_copy = xmlCopyNode(cur, 1); + + if (cur_copy == NULL) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_OUT_OF_MEMORY, + "could not copy node"); + + PG_TRY(); + { + xmlNodeDump(buf, NULL, cur_copy, 0, 1); + result = pstrdup((const char*) xmlBufferContent(buf)); + } + PG_CATCH(); + { + xmlFreeNode(cur_copy); + xmlBufferFree(buf); + PG_RE_THROW(); + } + PG_END_TRY(); + xmlFreeNode(cur_copy); + xmlBufferFree(buf); + } + else + { + xmlChar *str; + + str = xmlXPathCastNodeToString(cur); + PG_TRY(); + { + /* Here we rely on XML having the same representation as TEXT */ + char *escaped = escape_xml((char *) str); + + result = escaped; + } + PG_CATCH(); + { + xmlFree(str); + PG_RE_THROW(); + } + PG_END_TRY(); + xmlFree(str); + } + + return result; +} + +#endif /* USE_LIBXML */ + +/* + * Support for XMLTABLE function + */ +PgXmlTableContext * +makeXmlTableCxt(text *xpath_expr_text, xmltype *data, int ncols, char **paths, + FmgrInfo *in_functions, Oid *typioparams, TupleDesc tupdesc, + int ns_count, char **namespaces_uri, char **namespaces_name, + bool skip_second_search) +{ +#ifdef USE_LIBXML + PgXmlTableContext *cxt = palloc0(sizeof(struct PgXmlTableContext)); + PgXmlErrorContext *xmlerrcxt; + + volatile xmlParserCtxtPtr ctxt = NULL; + volatile xmlDocPtr doc = NULL; + volatile xmlXPathContextPtr xpathctx = NULL; + volatile xmlXPathCompExprPtr xpathcomp = NULL; + volatile xmlXPathObjectPtr xpathobj = NULL; + volatile xmlXPathCompExprPtr *xpathscomp = NULL; + volatile xmlXPathCompExprPtr _xpathcomp = NULL; + int32 len; + int32 xpath_len; + xmlChar *string; + xmlChar *xpath_expr; + int i; + + cxt->ncols = ncols; + cxt->paths = paths; + cxt->in_functions = in_functions; + cxt->typioparams = typioparams; + cxt->tupdesc = tupdesc; + cxt->rc = 0; + cxt->skip_second_search = true; + + cxt->xmlerrcxt = xmlerrcxt = pg_xml_init(PG_XML_STRICTNESS_ALL); + + len = VARSIZE(data) - VARHDRSZ; + xpath_len = VARSIZE(xpath_expr_text) - VARHDRSZ; + if (xpath_len == 0) + ereport(ERROR, + (errcode(ERRCODE_DATA_EXCEPTION), + errmsg("empty XPath expression"))); + + string = (xmlChar *) palloc((len + 1) * sizeof(xmlChar)); + memcpy(string, VARDATA(data), 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'; + + cxt->xpathscomp = xpathscomp = palloc0(sizeof(xmlXPathCompExprPtr) * ncols); + + PG_TRY(); + { + + xmlInitParser(); + + cxt->ctxt = ctxt = xmlNewParserCtxt(); + if (ctxt == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_OUT_OF_MEMORY, + "could not allocate parser context"); + cxt->doc = doc = xmlCtxtReadMemory(ctxt, (char *) string, len, NULL, NULL, 0); + if (doc == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT, + "could not parse XML document"); + cxt->xpathctx = xpathctx = xmlXPathNewContext(doc); + if (xpathctx == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_OUT_OF_MEMORY, + "could not allocate XPath context"); + xpathctx->node = xmlDocGetRootElement(doc); + if (xpathctx->node == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR, + "could not find root XML element"); + + /* register namespaces */ + for (i = 0; i < ns_count; i++) + { + if (xmlXPathRegisterNs(xpathctx, + (xmlChar *) namespaces_name[i], + (xmlChar *) namespaces_uri[i])) + elog(ERROR, "could not register XML namespace with name \"%s\" and URI \"%s\"", + namespaces_name[i], namespaces_uri[i]); + } + + cxt->xpathcomp = xpathcomp = xmlXPathCompile(xpath_expr); + if (xpathcomp == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR, + "invalid XPath expression"); + + cxt->xpathobj = xpathobj = xmlXPathCompiledEval(xpathcomp, xpathctx); + if (xpathobj == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR, + "could not create XPath object"); + + for(i = 0; i < ncols; i++) + { + int32 l = strlen(paths[i]); + xmlChar *buffer = palloc((l + 1) * sizeof(xmlChar)); + memcpy(buffer, paths[i], l); + buffer[l] = '\0'; + + _xpathcomp = xmlXPathCompile(buffer); + if (_xpathcomp == NULL || xmlerrcxt->err_occurred) + xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR, + "invalid XPath expression"); + cxt->xpathscomp[i] = _xpathcomp; + } + + } + PG_CATCH(); + { + XmlTableCxtFree(cxt); + PG_RE_THROW(); + } + PG_END_TRY(); + + return cxt; + +#else + NO_XML_SUPPORT(); + return NULL; +#endif /* not USE_LIBXML */ + +} + +void +XmlTableCxtFree(PgXmlTableContext *cxt) +{ +#ifdef USE_LIBXML + + if (cxt->xpathobj) + xmlXPathFreeObject(cxt->xpathobj); + if (cxt->xpathcomp) + xmlXPathFreeCompExpr(cxt->xpathcomp); + if (cxt->xpathctx) + xmlXPathFreeContext(cxt->xpathctx); + if (cxt->doc) + xmlFreeDoc(cxt->doc); + if (cxt->ctxt) + xmlFreeParserCtxt(cxt->ctxt); + + if (cxt->xpathscomp) + { + int i; + + for (i = 0; i < cxt->ncols; i++) + { + if (cxt->xpathscomp[i]) + { + xmlXPathFreeCompExpr(cxt->xpathscomp[i]); + cxt->xpathscomp[i] = NULL; + } + } + pfree((void *) cxt->xpathscomp); + cxt->xpathscomp = NULL; + } + + pg_xml_done(cxt->xmlerrcxt, true); + + pfree(cxt); +#else + NO_XML_SUPPORT(); + return; +#endif /* not USE_LIBXML */ +} + +bool +XmlTableGetRow(PgXmlTableContext *cxt) +{ +#ifdef USE_LIBXML + if (cxt->xpathobj->type == XPATH_NODESET) + { + if (cxt->xpathobj->nodesetval != NULL) + { + if (cxt->rc < cxt->xpathobj->nodesetval->nodeNr) + { + cxt->rc++; + return true; + } + } + } + + return false; +#else + NO_XML_SUPPORT(); + return false; +#endif /* not USE_LIBXML */ + +} + +Datum +XmlTableGetValue(PgXmlTableContext *cxt, int colnum, bool *isnull) +{ +#ifdef USE_LIBXML + volatile xmlXPathObjectPtr _xpathobj = NULL; + Datum value = (Datum) 0; + + if (cxt->xpathobj->type == XPATH_NODESET && + cxt->xpathobj->nodesetval != NULL) + { + xmlNodePtr cur; + + Assert(cxt->rc <= cxt->xpathobj->nodesetval->nodeNr); + + cur = cxt->xpathobj->nodesetval->nodeTab[cxt->rc - 1]; + + if (cur->type == XML_ELEMENT_NODE) + { + PG_TRY(); + { + xmlXPathSetContextNode(cur, cxt->xpathctx); + _xpathobj = xmlXPathCompiledEval(cxt->xpathscomp[colnum], cxt->xpathctx); + if (_xpathobj == NULL || cxt->xmlerrcxt->err_occurred) + xml_ereport(cxt->xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR, + "could not create XPath object"); + + switch (_xpathobj->type) + { + case XPATH_NODESET: + { + if (_xpathobj->nodesetval != NULL) + { + int result = _xpathobj->nodesetval->nodeNr; + + if (result == 0) + { + *isnull = true; + } + else if (result == 1) + { + char *cstr = xml_xmlnodetostr(_xpathobj->nodesetval->nodeTab[0], + cxt->xmlerrcxt); + + value = InputFunctionCall(&cxt->in_functions[colnum], + cstr, + cxt->typioparams[colnum], + cxt->tupdesc->attrs[colnum]->atttypmod); + *isnull = false; + } + else + { + StringInfoData str; + int i; + + if (cxt->tupdesc->attrs[colnum]->atttypid != XMLOID) + elog(ERROR, "too much rows"); + + initStringInfo(&str); + + for (i = 0; i < result; i++) + { + char *cstr; + + cstr = xml_xmlnodetostr(_xpathobj->nodesetval->nodeTab[i], + cxt->xmlerrcxt); + appendStringInfoString(&str, cstr); + } + + value = InputFunctionCall(&cxt->in_functions[colnum], + str.data, + cxt->typioparams[colnum], + cxt->tupdesc->attrs[colnum]->atttypmod); + *isnull = false; + } + } + else + *isnull = true; + } + break; + case XPATH_STRING: + value = InputFunctionCall(&cxt->in_functions[colnum], + (char *) _xpathobj->stringval, + cxt->typioparams[colnum], + cxt->tupdesc->attrs[colnum]->atttypmod); + *isnull = false; + break; + default: + elog(ERROR, "unexpected type"); + return 0; + } + + xmlXPathFreeObject(_xpathobj); + _xpathobj = NULL; + } + PG_CATCH(); + { + if (_xpathobj) + xmlXPathFreeObject(_xpathobj); + + PG_RE_THROW(); + } + PG_END_TRY(); + } + else + { + char *cstr; + + /* second search requires element */ + if (!cxt->skip_second_search) + elog(ERROR, "unexpected result"); + + cstr = xml_xmlnodetostr(cur, + cxt->xmlerrcxt); + + value = InputFunctionCall(&cxt->in_functions[colnum], + cstr, + cxt->typioparams[colnum], + cxt->tupdesc->attrs[colnum]->atttypmod); + + *isnull = false; + } + } + + Assert(*isnull || value != (Datum) 0); + + return value; + +#else + NO_XML_SUPPORT(); + return (Datum) 0; +#endif /* not USE_LIBXML */ + +} diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c index 5d179ae..64d6c9d 100644 --- a/src/backend/utils/fmgr/funcapi.c +++ b/src/backend/utils/fmgr/funcapi.c @@ -20,6 +20,7 @@ #include "funcapi.h" #include "nodes/nodeFuncs.h" #include "parser/parse_coerce.h" +#include "parser/parse_type.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/lsyscache.h" @@ -39,7 +40,9 @@ static bool resolve_polymorphic_tupdesc(TupleDesc tupdesc, oidvector *declared_args, Node *call_expr); static TypeFuncClass get_type_func_class(Oid typid); - +static TypeFuncClass xmltable_get_result_type(XmlTable *expr, + Oid *resultTypeId, + TupleDesc *resultTupleDesc); /* * init_MultiFuncCall @@ -243,6 +246,10 @@ get_expr_result_type(Node *expr, NULL, resultTypeId, resultTupleDesc); + else if (expr && IsA(expr, XmlTable)) + result = xmltable_get_result_type((XmlTable *) expr, + resultTypeId, + resultTupleDesc); else { /* handle as a generic expression; no chance to resolve RECORD */ @@ -1398,3 +1405,48 @@ TypeGetTupleDesc(Oid typeoid, List *colaliases) return tupdesc; } + +static TypeFuncClass +xmltable_get_result_type(XmlTable *xt, + Oid *resultTypeId, + TupleDesc *resultTupleDesc) +{ + ListCell *l; + int i = 0; + TupleDesc tupdesc; + + tupdesc = CreateTemplateTupleDesc(list_length(xt->coldeflist), false); + + foreach(l, xt->coldeflist) + { + ColumnDef *def = (ColumnDef *) lfirst(l); + Oid typId; + int32 typmod; + + if (def->for_ordinality) + { + typId = INT8OID; + typmod = -1; + } + else + typenameTypeIdAndMod(NULL, def->typeName, &typId, &typmod); + + TupleDescInitEntry(tupdesc, i + 1, def->colname, typId, -1, 0); + + tupdesc->attrs[i]->attnotnull = def->is_not_null; + + i++; + } + + if (resultTypeId) + *resultTypeId = RECORDOID; + + if (tupdesc->tdtypeid == RECORDOID && + tupdesc->tdtypmod < 0) + assign_record_type_typmod(tupdesc); + if (resultTupleDesc) + *resultTupleDesc = tupdesc; + + return TYPEFUNC_COMPOSITE; +} + diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index e7fd7bd..90fe9dd 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -959,6 +959,27 @@ typedef struct XmlExprState } XmlExprState; /* ---------------- + * XmlTableState node + * ---------------- + */ +typedef struct XmlTableState +{ + ExprState xprstate; + List *namespaces; /* list of ResTarget namespaces */ + ExprState *query_string; /* ExprStates for query string */ + ExprState *expr; /* ExprStates for XML parameter */ + int ncolumns; /* number of columns */ + ExprState **path_exprs; /* ExprStates for column PATH expressions */ + ExprState **default_exprs; /* ExprStates for defaults */ + ReturnSetInfo *rsinfo; /* link to outer world */ + FmgrInfo *in_functions; /* array of input function for each column */ + Oid *typioparams; /* array of column types for in_function */ + bool *is_not_nulls; /* allow or disallow null */ + int ordinality_colnum; /* number of column with ordinary */ + bool with_auto_col; /* one result column is automatic */ +} XmlTableState; + +/* ---------------- * NullTestState node * ---------------- */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 6b850e4..d255c4a 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -212,6 +212,7 @@ typedef enum NodeTag T_CoalesceExprState, T_MinMaxExprState, T_XmlExprState, + T_XmlTableState, T_NullTestState, T_CoerceToDomainState, T_DomainConstraintState, @@ -447,6 +448,7 @@ typedef enum NodeTag T_LockingClause, T_RowMarkClause, T_XmlSerialize, + T_XmlTable, T_WithClause, T_InferClause, T_OnConflictClause, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 1481fff..71f8ff1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -565,6 +565,7 @@ typedef struct RangeTableSample int location; /* method name location, or -1 if unknown */ } RangeTableSample; + /* * ColumnDef - column definition (used in various creates) * @@ -592,9 +593,12 @@ typedef struct ColumnDef bool is_local; /* column has local (non-inherited) def'n */ bool is_not_null; /* NOT NULL constraint specified? */ bool is_from_type; /* column definition came from table type */ + bool for_ordinality; /* column will contains row numbers */ char storage; /* attstorage setting, or 0 for default */ Node *raw_default; /* default value (untransformed parse tree) */ Node *cooked_default; /* default value (transformed expr tree) */ + Node *raw_path; /* path value (untransformed parse tree) */ + Node *cooked_path; /* path value (transformed expr tree *) */ CollateClause *collClause; /* untransformed COLLATE spec, if any */ Oid collOid; /* collation OID (InvalidOid if not set) */ List *constraints; /* other constraints on column */ @@ -697,6 +701,33 @@ typedef struct XmlSerialize int location; /* token location, or -1 if unknown */ } XmlSerialize; +/* + * XmlTable - implementation of Xmltable function + * + */ +typedef enum +{ + XMLTABLE_COLUMN_OPT_PATH, + XMLTABLE_COLUMN_OPT_DEFAULT +} XmlTableColOptType; + +typedef struct +{ + XmlTableColOptType typ; + Node *expr; +} XmlTableColOpt; + +typedef struct XmlTable +{ + NodeTag type; + List *namespaces; /* The list of namespaces - optional */ + Node *query_string; /* XPath query for row selection */ + Node *expr; /* XML content */ + List *coldeflist; /* definition of columns of produced table */ + bool with_ordinality; /* true when ordinality column is used */ + bool with_auto_col; /* result column is automatic */ + int location; /* method name location, or -1 if unknown */ +} XmlTable; /**************************************************************************** * Nodes for a Query tree diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 17ffef5..de83895 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -81,6 +81,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) @@ -289,6 +290,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD) +PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD) @@ -438,10 +440,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/utils/xml.h b/src/include/utils/xml.h index 2eab8a5..0cac321 100644 --- a/src/include/utils/xml.h +++ b/src/include/utils/xml.h @@ -45,6 +45,7 @@ typedef enum /* struct PgXmlErrorContext is private to xml.c */ typedef struct PgXmlErrorContext PgXmlErrorContext; +typedef struct PgXmlTableContext PgXmlTableContext; #define DatumGetXmlP(X) ((xmltype *) PG_DETOAST_DATUM(X)) #define XmlPGetDatum(X) PointerGetDatum(X) @@ -109,4 +110,12 @@ extern int xmlbinary; /* XmlBinaryType, but int for guc enum */ extern int xmloption; /* XmlOptionType, but int for guc enum */ +extern PgXmlTableContext *makeXmlTableCxt(text *xpath_expr_text, xmltype *data, int ncols, char **paths, + FmgrInfo *in_functions, Oid *typioparams, TupleDesc tupdesc, + int nnamespaces, char **namespaces_uri, char **namespaces_name, + bool skip_second_search); +extern void XmlTableCxtFree(PgXmlTableContext *cxt); +extern bool XmlTableGetRow(PgXmlTableContext *cxt); +extern Datum XmlTableGetValue(PgXmlTableContext *cxt, int colnum, bool *isnull); + #endif /* XML_H */ diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out index f21e119..a80725b 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -948,3 +948,98 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4 <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter> (1 row) +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES('<ROWS> +<ROW id="1"> + <COUNTRY_ID>AU</COUNTRY_ID> + <COUNTRY_NAME>Australia</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="2"> + <COUNTRY_ID>CN</COUNTRY_ID> + <COUNTRY_NAME>China</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="3"> + <COUNTRY_ID>HK</COUNTRY_ID> + <COUNTRY_NAME>HongKong</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="4"> + <COUNTRY_ID>IN</COUNTRY_ID> + <COUNTRY_NAME>India</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="5"> + <COUNTRY_ID>JP</COUNTRY_ID> + <COUNTRY_NAME>Japan</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME> +</ROW> +<ROW id="6"> + <COUNTRY_ID>SG</COUNTRY_ID> + <COUNTRY_NAME>Singapore</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE> +</ROW> +</ROWS>'); +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); + xmltable +------------- + <row> + + <a>10</a>+ + <a>20</a>+ + </row> +(1 row) + +SELECT XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); +ERROR: xmltable function is called in unsupported expression context +-- 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) + +-- XMLNAMESPACES tests +SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz) + '/zz:rows/zz:row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'zz:a'); + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y') + '/rows/row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'a'); + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); + xmltable +---------- + 10 + 20 +(2 rows) + diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out index d702703..86a1e6f 100644 --- a/src/test/regress/expected/xml_1.out +++ b/src/test/regress/expected/xml_1.out @@ -827,3 +827,97 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4 ERROR: unsupported XML feature DETAIL: This functionality requires the server to be built with libxml support. HINT: You need to rebuild PostgreSQL using --with-libxml. +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES('<ROWS> +<ROW id="1"> + <COUNTRY_ID>AU</COUNTRY_ID> + <COUNTRY_NAME>Australia</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="2"> + <COUNTRY_ID>CN</COUNTRY_ID> + <COUNTRY_NAME>China</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="3"> + <COUNTRY_ID>HK</COUNTRY_ID> + <COUNTRY_NAME>HongKong</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="4"> + <COUNTRY_ID>IN</COUNTRY_ID> + <COUNTRY_NAME>India</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="5"> + <COUNTRY_ID>JP</COUNTRY_ID> + <COUNTRY_NAME>Japan</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME> +</ROW> +<ROW id="6"> + <COUNTRY_ID>SG</COUNTRY_ID> + <COUNTRY_NAME>Singapore</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE> +</ROW> +</ROWS>'); +ERROR: unsupported XML feature +LINE 1: INSERT INTO xmldata VALUES('<ROWS> + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +SELECT XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); +ERROR: unsupported XML feature +LINE 1: SELECT XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>... + ^ +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) + +-- XMLNAMESPACES tests +SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz) + '/zz:rows/zz:row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'zz:a'); +ERROR: unsupported XML feature +LINE 3: PASSING '<rows xmlns="http://x.y"><row... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y') + '/rows/row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'a'); +ERROR: unsupported XML feature +LINE 3: PASSING '<rows xmlns="http://x.y"><row... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); +ERROR: unsupported XML feature +LINE 1: ...LECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><ro... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. diff --git a/src/test/regress/expected/xml_2.out b/src/test/regress/expected/xml_2.out index 530faf5..fb03d8d 100644 --- a/src/test/regress/expected/xml_2.out +++ b/src/test/regress/expected/xml_2.out @@ -928,3 +928,98 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4 <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter> (1 row) +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES('<ROWS> +<ROW id="1"> + <COUNTRY_ID>AU</COUNTRY_ID> + <COUNTRY_NAME>Australia</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="2"> + <COUNTRY_ID>CN</COUNTRY_ID> + <COUNTRY_NAME>China</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="3"> + <COUNTRY_ID>HK</COUNTRY_ID> + <COUNTRY_NAME>HongKong</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="4"> + <COUNTRY_ID>IN</COUNTRY_ID> + <COUNTRY_NAME>India</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="5"> + <COUNTRY_ID>JP</COUNTRY_ID> + <COUNTRY_NAME>Japan</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME> +</ROW> +<ROW id="6"> + <COUNTRY_ID>SG</COUNTRY_ID> + <COUNTRY_NAME>Singapore</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE> +</ROW> +</ROWS>'); +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); + xmltable +------------- + <row> + + <a>10</a>+ + <a>20</a>+ + </row> +(1 row) + +SELECT XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); +ERROR: xmltable function is called in unsupported expression context +-- 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) + +-- XMLNAMESPACES tests +SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz) + '/zz:rows/zz:row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'zz:a'); + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y') + '/rows/row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'a'); + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); + xmltable +---------- + 10 + 20 +(2 rows) + diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql index 08a0b30..8599084 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -270,3 +270,69 @@ SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo> SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>'); -- This might or might not load the requested DTD, but it mustn't throw error. SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>'); + +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES('<ROWS> +<ROW id="1"> + <COUNTRY_ID>AU</COUNTRY_ID> + <COUNTRY_NAME>Australia</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="2"> + <COUNTRY_ID>CN</COUNTRY_ID> + <COUNTRY_NAME>China</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="3"> + <COUNTRY_ID>HK</COUNTRY_ID> + <COUNTRY_NAME>HongKong</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="4"> + <COUNTRY_ID>IN</COUNTRY_ID> + <COUNTRY_NAME>India</COUNTRY_NAME> + <REGION_ID>3</REGION_ID> +</ROW> +<ROW id="5"> + <COUNTRY_ID>JP</COUNTRY_ID> + <COUNTRY_NAME>Japan</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME> +</ROW> +<ROW id="6"> + <COUNTRY_ID>SG</COUNTRY_ID> + <COUNTRY_NAME>Singapore</COUNTRY_NAME> + <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE> +</ROW> +</ROWS>'); + +-- XMLTABLE without columns +SELECT * FROM XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); +SELECT XMLTABLE('/rows/row' PASSING '<rows><row><a>10</a><a>20</a></row></rows>'); + +-- XMLTABLE with columns +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); + +-- XMLNAMESPACES tests +SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz) + '/zz:rows/zz:row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'zz:a'); + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y') + '/rows/row' + PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' + COLUMNS a int PATH 'a'); + +SELECT * FROM XMLTABLE('/rows/row/a/text()' PASSING '<rows><row><a>10</a><a>20</a></row></rows>');
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers