ne 20. 1. 2019 v 5:37 odesÃlatel Chapman Flack <c...@anastigmatix.net> napsal:
> 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.) > If I remember, described functionality was implemented in early patches, but was removed to simplify code. To now, there was not a request to do it. Unfortunately, the documentation was not fixed. > > - 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.) > column expressions are evaluated once per row, but XPath expression is compiled per row too, if I remember well. We designed it more tolerant as we expected possibility to store XPath expression in one column and data in second column. Regards Pavel > > 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 >
