From fcec6caafa2346b6c9d3ad5065e417733bd63cd9 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 8 Mar 2017 12:39:37 -0300 Subject: Support XMLTABLE query expression MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit XMLTABLE is defined by the SQL/XML standard as a feature that allows turning XML-formatted data into relational form, so that it can be used as a in the FROM clause of a query. This new construct provides significant simplicity and performance benefit for XML data processing; what in a client-side custom implementation was reported to take 20 minutes can be executed in 400ms using XMLTABLE. (The same functionality was said to take 10 seconds using nested PostgreSQL XPath function calls, and 5 seconds using XMLReader under PL/Python). The implemented syntax deviates slightly from what the standard requires. First, the standard indicates that the PASSING clause is optional and that multiple XML input documents may be given to it; we make it mandatory and accept a single document only. Second, we don't currently support a default namespace to be specified. This implementation relies on a new executor node based on a hardcoded method table. (Because the grammar is fixed, there is no extensibility in the current approach; further constructs can be implemented on top of this such as JSON_TABLE, but they require changes to core code.) Author: Pavel Stehule, Álvaro Herrera Extensively reviewed by: Craig Ringer Discussion: https://postgr.es/m/CAFj8pRAgfzMD-LoSmnMGybD0WsEznLHWap8DO79+-GTRAPR4qA@mail.gmail.com --- src/backend/utils/adt/ruleutils.c | 133 +++++++++++++++++++++++++++++++++++++- 1 file changed, 130 insertions(+), 3 deletions(-) (limited to 'src/backend/utils/adt/ruleutils.c') diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index cf79f4126e4..3c697f3c0df 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -429,6 +429,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, @@ -3642,14 +3644,17 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, * are different from the underlying "real" names. For a function RTE, * always emit a complete column alias list; this is to protect against * possible instability of the default column names (eg, from altering - * parameter names). For other RTE types, print if we changed anything OR - * if there were user-written column aliases (since the latter would be - * part of the underlying "reality"). + * parameter names). For tablefunc RTEs, we never print aliases, because + * the column names are part of the clause itself. For other RTE types, + * print if we changed anything OR if there were user-written column + * aliases (since the latter would be part of the underlying "reality"). */ if (rte->rtekind == RTE_RELATION) colinfo->printaliases = changed_any; else if (rte->rtekind == RTE_FUNCTION) colinfo->printaliases = true; + else if (rte->rtekind == RTE_TABLEFUNC) + colinfo->printaliases = false; else if (rte->alias && rte->alias->colnames != NIL) colinfo->printaliases = true; else @@ -6726,6 +6731,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 @@ -8562,6 +8568,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; @@ -9297,6 +9307,120 @@ 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; + + /* XMLTABLE is the only existing implementation. */ + + 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->colexprs != NIL) + { + 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 * @@ -9530,6 +9654,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, '('); -- cgit v1.2.3