Working slowly through the documentation, I came upon:
For XMLTABLE:
- The xmltable function produces a table based on the given XML value,
an XPath filter to extract rows, and an optional set of column
definitions. ^^^^^^^^
...
The mandatory COLUMNS clause specifies the list of columns ...
^^^^^^^^^
if the COLUMNS clause is omitted, the rows in the result set contain
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
a single column of type xml containing the data matched by
row_expression.
This documentation seems undecided on whether the COLUMNS clause
is mandatory or optional.
It is mandatory in the SQL standard. It's mandatory in our grammar.
We give a syntax_error if it's omitted.
Is some of the documentation left over from an earlier contemplated
design of having the clause be optional?
Oracle does seem to allow the clause to be omitted, and produces a
single xml column, as described. Was there an earlier plan to imitate
Oracle's nonstandard behavior on that point? (Hardly seems worth the
effort, as porting an Oracle query depending on it would simply entail
adding COLUMNS COLUMN_VALUE XML PATH '.' and then it's portable and
standard.)
- 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.
Is there an example that clearly shows this to work? If I write a
default_expression referring to a prior column in /xmltable's own/
column list, I get an undefined_column error. I can successfully refer
to a column of /an earlier FROM item in the SELECT/, but I am not sure
that demonstrates the behavior claimed here.
There is what looks like an example among the regression tests
(the one with DEFAULT ascii(_path) - 54), but that seems only to
demonstrate xmltable getting invoked four times (as documented for
LATERAL), not a single xmltable invocation producing multiple rows
with recomputed defaults.
If it's any comfort, I haven't gotten Oracle's xmltable to recognize
earlier columns in its own column list either.
- Unlike regular PostgreSQL functions, column_expression and
default_expression are not evaluated to a simple value before calling
the function. column_expression is normally evaluated exactly once
per input row, and default_expression is evaluated each time a default
is needed for a field.
I've already covered the question about default_expression, but what
this passage says about column_expression seems, at least, ambiguously
worded, too:
It goes without saying that /the XPath evaluator/ evaluates the
column_expression exactly once per input row. In the standard, that's
the only per-row evaluation happening; the column_expression SQL value
only gets compiled to an XPath expression once at the start. (In fact,
in the standard, it can't even be an arbitrary SQL expression, only a
string literal. Oracle enforces that too.)
It seems that our implementation is meant to extend the standard and
actually allow the column_expression to vary per-row, and go through
the XPath expression compiler each time. The regression test with
COLUMNS a int PATH '' || lower(_path) || 'c'
seems to be intended to confirm that behavior. But again, I think
it is only confirming that LATERAL results in xmltable being called
four consecutive times, with a different PATH in each call. It does
not seem to demonstrate a single xmltable call doing anything special
with recompiling a column path.
Am I overlooking something?
Regards,
-Chap
