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.
