Re: PostgreSQL vs SQL/XML Standards

On 01/20/19 17:13, Chapman Flack wrote:
> On 01/20/19 12:48, Pavel Stehule wrote:
>> regular Postgres' functions has evaluated all arguments before own
>> execution. I think so this note is related much more to expressions used as
>> defaults.
>
> Sure, but again, is there an example, or can one easily be constructed,
> that shows the default expressions working in such a way?

To make my question more concrete, here is the current regression test
query that uses an SQL expression for a default:

SELECT xmltable.*
FROM
xmltest2,
xmltable(('/d/r/' || lower(_path) || 'c')
PASSING x
COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
a
----
11
12
13
14
(4 rows)

Here is the same query desugared into a call of the PL/Java "xmltable":

SELECT xmltable.*
FROM
xmltest2,
LATERAL (SELECT x AS ".", ascii(_path) - 54 AS "A_DFT") AS p,
"xmltable"(('/d/r/' || lower(_path) || 'c'),
PASSING => p,
COLUMNS => ARRAY[
'let $a := x return xs:int(if (exists($a)) then $a else $A_DFT)'
]
) AS (a int);
a
----
11
12
13
14
(4 rows)

So the PL/Java version works and produces the same results. And yet
it certainly is a "regular PostgreSQL function" made with CREATE FUNCTION,
no special treatment of arguments, all evaluated before entry in the usual
way.

So it appears that this example does not depend on any special treatment
of the default_expression.

Is there an example that can be constructed that would depend on the
special treatment (in which case, the PL/Java implementation would be
unable to produce the same result)?

Regards,
-Chap

... the xs:int(...) cast above is needed for now, just because the PL/Java
implementation does not yet include the standard's algorithm to find
the right cast automatically.