Re: PostgreSQL vs SQL/XML Standards

ne 20. 1. 2019 v 5:37 odesílatel Chapman Flack <chap(at)anastigmatix(dot)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
>