Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters - Mailing list pgsql-bugs

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

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.

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

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company