Re: PostgreSQL vs SQL/XML Standards - Mailing list pgsql-hackers

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