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@gmail.com> wrote:

> On Sat, Dec 5, 2015 at 12:30 AM,  <mike.lang1010@gmail.com> wrote:
>
> > I've found that when a user defined function has
> > out parameters, it is invoked once per out parameter if invoked with th=
e
> > 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:
>

=E2=80=8BYou can see "what" happens.  The why is still a mystery...


> test=3D# explain (analyze, buffers, verbose) SELECT (reproduceBehavior())=
.*;
>                                                                 QUERY PLA=
N
>
> -------------------------------------------------------------------------=
-----------------------------------------------------------------
>  Result  (cost=3D0.00..1.01 rows=3D1 width=3D0) (actual time=3D1.931..1.9=
32
> rows=3D1 loops=3D1)
>    Output: (reproducebehavior()).message1,
> (reproducebehavior()).message2, (reproducebehavior()).message3,
> (reproducebehavior()).message4
>    Buffers: shared hit=3D17
>  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.
>
>
=E2=80=8BWhile you've explained how to see what is happening it doesn't rem=
ove the
POLA violation that has occurred here.=E2=80=8B

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

=E2=80=8BOK - 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.