Re: patch: function xmltable - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Re: patch: function xmltable
Date
Msg-id 0dbb5659-2c1a-c171-b25b-d3f1cd42f4a9@2ndquadrant.com
Whole thread Raw
In response to Re: patch: function xmltable  (Craig Ringer <craig@2ndquadrant.com>)
List pgsql-hackers
On 9/12/16 1:14 AM, Craig Ringer wrote:
> I would've expected once per query. There's no way the expressions can
> reference the row data, so there's no reason to evaluate them each
> time.
> 
> The only use case I see for evaluating them each time is - maybe -
> DEFAULT. Where maybe there's a use for nextval() or other volatile
> functions. But honestly, I think that's better done explicitly in a
> post-pass, i.e.
> 
> select uuid_generate_v4(), x.*
> from (
>   xmltable(.....) x
> );
> 
> in cases where that's what the user actually wants.
> 
> There's no other case I can think of where expressions as arguments to
> set-returning functions are evaluated once per output row.

The SQL standard appears to show what the behavior ought to be:

<XML table> is equivalent to

LATERAL ( XNDC
SELECT SLI1 AS CN1, SLI2 AS CN2, ..., SLINC AS CNNC FROM XMLITERATE (
XMLQUERY ( XTRP XQAL                        RETURNING SEQUENCE BY REF EMPTY ON EMPTY ) )           AS I ( V, N )
) AS CORR DCLP

and SLIj is

CASE WHEN XEj
THEN XMLCAST( XQCj AS DTj CPMj )
ELSE DEFj END

where DEFj is the default expression.

So simplified it is

LATERAL ( SELECT CASE WHEN ... ELSE DEFj END, ... FROM something )

which indicates that the default expression is evaluated for every row.

If we're not sure about all this, it might be worth restricting the
default expressions to stable or immutable expressions for the time being.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: WIP: About CMake v2
Next
From: Michael Paquier
Date:
Subject: Re: WAL consistency check facility