Re: PostgreSQL vs SQL/XML Standards

ne 20. 1. 2019 v 5:37 odesílatel Chapman Flack <chap@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
>