Re: PostgreSQL vs SQL/XML Standards

ne 20. 1. 2019 v 17:06 odesílatel Chapman Flack <chap(at)anastigmatix(dot)net>
napsal:

> Hi,
>
> On 01/20/19 00:26, Pavel Stehule wrote:
> >>> column expressions are evaluated once per row, but XPath expression is
> >>> compiled per row too, if I remember well.
> >>
> >> I looked for evidence of that in the code, but did not find it; the
> >> compilation appears to happen in XmlTableSetColumnFilter, which is
> >> called from tfuncInitialize.
> >
> > it is called per input row.
>
> I could be misunderstanding what you mean by 'input row' here.
>

input row mean a row of processed relation. The xml value from this row can
be transformed to 0..M output rows.

The column filter expressions are evaluated once per input rows, default
expressions are evaluated when it is necessary - possibly once for any
output row

>
> If I write a simple xmltable query where the row_expression produces
> six rows:
>
> SELECT *
> FROM
> xmltable('pg_am/row'
> PASSING table_to_xml('pg_am', true, false, '')
> COLUMNS amname text PATH 'amname');
>
> six rows are produced, though a breakpoint set on XmlTableSetColumnFilter
> fires only once, from tfuncInitialize at the start of xmltable's execution.
>
>
> By contrast, in the regression test example with PATH
> ''||lower(_path)||'c',
> four rows are produced and the breakpoint fires four times.
>

it is expected - the input relation has four lines - the function was 4x
initialized. In this case 1 call of xmltable produces 1 row.

>
> However ... that isn't because one call to xmltable is producing four rows
> and recomputing the column_expression each time.
>
> It's because that xmltable is the RHS of a LATERAL, and the LHS of the
> LATERAL is producing four tuples with different values of columns the RHS
> depends on, so the RHS (xmltable) is being called four different times,
> producing one row each time, still with XmlTableSetColumnFilter being
> called
> only during initialization.
>
>
> > sure - using any expression in PATH clause should to demonstrate this
> > functionality.
>
> Well, there seem to be two distinct features touched on in the docs:
>
> 1. The column_expression is allowed to be an expression, not restricted
> to a string literal as it is in the standard (and Oracle).
>
> 2. Not only is it an expression, but it's an expression whose evaluation
> is deferred and can happen more than once in the same xmltable call.
>

yes, it is any expressions used there are evaluated per 1 call. If input
relation has N rows, then xmltable is initialized N times - by different
words. xmltable is evaluated independently for any processed XML document.

> The example in the regression tests certainly demonstrates (1). Without
> (1),
> it would be a syntax error.
>
> I think the same example would produce the same output even with feature
> (2)
> absent. It's LATERAL doing the magic there. So I am doubtful that it
> demonstrates (2).
>

LATERAL is necessary, because XMLTABLE can be used only in FROM clause,
and in this case XMLTABLE has mutable parameters.

> I put some effort last night into trying to even construct any query that
> would demonstrate (2), and I came up short, but that could be my lack of
> imagination. (Somewhere in that effort I happened to notice that xmltable
> doesn't seem to be parsed successfully inside a ROWS FROM (...) construct,
> which might be another issue for another time....)
>
> So, if you have a way to build a query that demonstrates (2), my aha!
> moment
> might then arrive.
>
> Regards,
> -Chap
>