Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters

On Tue, Dec 8, 2015 at 9:08 AM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:

> On Sat, Dec 5, 2015 at 12:30 AM, <mike(dot)lang1010(at)gmail(dot)com> wrote:
>
> > I've found that when a user defined function has
> > out parameters, it is invoked once per out parameter if invoked with the
> > syntax:
> >
> > `SELECT (udf()).*`
> >
> > Is this the expected behavior? It seems like it shouldn't.
>
> You can see why this happens if you use EXPLAIN this way:
>

​You can see "what" happens. The why is still a mystery...

> test=# explain (analyze, buffers, verbose) SELECT (reproduceBehavior()).*;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------------
> Result (cost=0.00..1.01 rows=1 width=0) (actual time=1.931..1.932
> rows=1 loops=1)
> Output: (reproducebehavior()).message1,
> (reproducebehavior()).message2, (reproducebehavior()).message3,
> (reproducebehavior()).message4
> Buffers: shared hit=17
> Planning time: 0.038 ms
> Execution time: 1.968 ms
> (5 rows)
>
> That shows that the * causes expansion to the following query:
>
> SELECT
> (reproducebehavior()).message1,
> (reproducebehavior()).message2,
> (reproducebehavior()).message3,
> (reproducebehavior()).message4;
>
> From that you can see why it is not surprising that the function is
> executed once per OUT parameter, especially if it is VOLATILE.
>
>
​While you've explained how to see what is happening it doesn't remove the
POLA violation that has occurred here.​

You seem to be expecting it to behave like this:
>
> SELECT * FROM (SELECT * FROM reproduceBehavior()) x;
>
> Which is interpreted as:
>
> SELECT x.message1, x.message2, x.message3, x.message4
> FROM (SELECT * FROM reproduceBehavior()) x;
>
> To avoid surprises, avoid using *
>

​OK - but how is one supposed to do that? There is no good way to explode
a composite type, especially one created using a function, without using *.

I've responded to the original thread with two possible alternative query
forms. The CTE one is a hack while the implementation of LATERAL finally
provided a non-hackey means to accomplish the goal. The behavior of
(SELECT (func_call()).*) will likely never change but I'd still argue that
not repeatedly invoking the function would be the better implementation and
the least astonishing one.

David J.