Subject: Re: PostgreSQL vs SQL/XML Standards
To: Pavel Stehule <pavel.stehule@gmail.com>
References: 
 <CAFj8pRDFQJMoKf-X+3p1ASwU02nnXfAXrg83Z3fHUBd-Gwuuxw@mail.gmail.com>
 <45C74339-3531-46BD-A595-37183C6730A1@winand.at>
 <CAFj8pRB4NHismecE-SnezKz9OektP2H8t_684TbqQ4Ax3swZwQ@mail.gmail.com>
 <5C283168.8060001@anastigmatix.net>
 <CAFj8pRDRrxyr8=2PEFxP_eZCRqJMU4Jpj0whJM9pX0V4Q4EQvw@mail.gmail.com>
 <5C291741.9060506@anastigmatix.net>
 <CAFj8pRAmSUO1g1qnVgXmE=7bPULYjwDfyO5KDohsHMFesDenmA@mail.gmail.com>
 <5C297BB7.9070509@anastigmatix.net>
 <CAFj8pRCN=0k507nTfvNQuruHvomA4SiC6uGUXY5Gz0crxf-JAQ@mail.gmail.com>
 <5C3A7A90.3070207@anastigmatix.net>
 <CAFj8pRCSa0ekwc8AOUnEsLeJhWbq7_rOyFzruopfgBTaMpUWMA@mail.gmail.com>
 <5C43FAEB.2070000@anastigmatix.net>
 <CAFj8pRDLywawDY-dRsYdjF036iR_Y4em77X1DF7VodUqau_0Vg@mail.gmail.com>
 <5C4401BE.4080606@anastigmatix.net>
 <CAFj8pRCKwDGqP55RTykDq87DXuXStE+3Qy2Hf9Wxh_A-cUnwTw@mail.gmail.com>
 <5C449C71.7060903@anastigmatix.net>
 <CAFj8pRAabQWW13FS58fj1h6YxKsbhEaegRctf28TFeheWGn_3A@mail.gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5C44B332.6070609@anastigmatix.net>
Date: Sun, 20 Jan 2019 12:43:14 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFj8pRAabQWW13FS58fj1h6YxKsbhEaegRctf28TFeheWGn_3A@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 01/20/19 11:55, Pavel Stehule wrote:
> 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
> ...
> it is expected - the input relation has four lines - the function was 4x
> initialized. In this case 1 call of xmltable produces 1 row.

Good ... I think we're converging on a shared understanding.

I am just used to speaking of xmltable as a simple function that executes
one row_expression one time against one supplied input, and generates 0..M
output rows from it. If there are multiple rows in a relation that you
want to apply xmltable to, that's a simple matter of calling xmltable
multiple times (which is just what SQL is doing when the xmltable is on
the RHS of an explicit or implied LATERAL).

The upshot seems to be that there is nothing necessarily special about
how xmltable treats its column_expressions: it compiles them once upon
entry to the function, as one would naïvely expect. (Or, if there is
anything more special about how the column_expression is being handled,
it seems not to be necessary, as the naïve behavior would be adequate.)

Accordingly, I think the paragraph beginning "Unlike regular PostgreSQL
functions," is more likely to perplex readers than to enlighten them.
What it says about column_expression does not seem to lead to any useful
difference from the behavior if it were "just like regular PostgreSQL
functions".

The part about usefully using volatile functions in default_expression
remains important to mention.

The statement in an earlier paragraph that "It is possible for a
default_expression to reference the value of output columns that appear
prior to it in the column list" still may need some rework, because it
does not seem possible to refer to prior columns /within xmltable's own
column list/ (though that could be useful, and I think it is intended
in the standard). Doesn't seem to work in Oracle either....

While it does seem possible to refer to columns supplied by
/earlier FROM items in the containing SELECT/, that simply results in
multiple calls of xmltable, just as in the column_expression case.

>> 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.

For what it's worth, if I repeat the query with the word LATERAL removed,
it works just the same. I think that's simply because the LATERAL behavior
is implied for a function-call FROM item, so the explicit word isn't needed.
The main thing is, evaluation proceeds in the way described under LATERAL
in the ref page for SELECT.

Regards,
-Chap