Multiple evaluation of single reference to function with out parameters
Hi,
If I create a function with more than one out parameter, and then refer to
it inside parentheses as a record, e.g. select (function()).*, the function
appears to be evaluated multiple times, once for every column returned.
This seems to be true regardless of whether it's defined as volatile or
immutable.
Here's an example:
# create or replace function foobar(out foo integer, out bar integer)
volatile language plpgsql as $$ begin raise notice 'Called'; foo := 1; bar
:= 2; end; $$;
CREATE FUNCTION
If I call it the usual way, it's only evaluated once:
# select * from foobar();
NOTICE: 00000: Called
LOCATION: exec_stmt_raise, pl_exec.c:3165
foo | bar
-----+-----
1 | 2
(1 row)
Here the function was called once, and the results are returned correctly.
However, if I call it this way,
# select (foobar()).*;
NOTICE: 00000: Called
LOCATION: exec_stmt_raise, pl_exec.c:3165
NOTICE: 00000: Called
LOCATION: exec_stmt_raise, pl_exec.c:3165
foo | bar
-----+-----
1 | 2
(1 row)
This way the function seems to be called separately for each column it
returns, but the results are only returned once. If I define it with three
out parameters, it's called three times.
As far as I can tell, this behavior has been the same since at least
version 8.2 and up through 10 beta 4, but I can't find any references to it
and it seems very surprising. It could certainly cause unexpected results
if the function has side effects. Is this a bug?
Joel
On Friday, September 22, 2017, Joel Hoffman <joel.hoffman@gmail.com> wrote:
If I create a function with more than one out parameter, and then refer to
it inside parentheses as a record, e.g. select (function()).*, the function
appears to be evaluated multiple times, once for every column returned.
This seems to be true regardless of whether it's defined as volatile or
immutable....
As far as I can tell, this behavior has been the same since at least
version 8.2 and up through 10 beta 4, but I can't find any references to it
and it seems very surprising. It could certainly cause unexpected results
if the function has side effects. Is this a bug?
It is not a bug or likely to be fixed. LATERAL makes the need for function
invocation in the manner you describe nearly unnecessary and you can use
OFFSET 0 in other cases to put the function call in a subquery and place
the (composite).* expressionin the upper-level.
There is a cautionary note somewhere in docs about this. It is a parser
byproduct. The star gets expanded at parse time to individual and
independent column names. What happens is exactly what you'd expect if you
tried to write the query without resorting to using ".*"
David J.
Thanks for the response. Here's the documentation reference:
https://www.postgresql.org/docs/9.6/static/rowtypes.html#ROWTYPES-USAGE
Not a bug, but I do think it's very surprising behavior.
Joel
On Fri, Sep 22, 2017 at 5:22 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Friday, September 22, 2017, Joel Hoffman <joel.hoffman@gmail.com>
wrote:If I create a function with more than one out parameter, and then refer
to it inside parentheses as a record, e.g. select (function()).*, the
function appears to be evaluated multiple times, once for every column
returned. This seems to be true regardless of whether it's defined as
volatile or immutable....
As far as I can tell, this behavior has been the same since at least
version 8.2 and up through 10 beta 4, but I can't find any references to it
and it seems very surprising. It could certainly cause unexpected results
if the function has side effects. Is this a bug?It is not a bug or likely to be fixed. LATERAL makes the need for
function invocation in the manner you describe nearly unnecessary and you
can use OFFSET 0 in other cases to put the function call in a subquery and
place the (composite).* expressionin the upper-level.There is a cautionary note somewhere in docs about this. It is a parser
byproduct. The star gets expanded at parse time to individual and
independent column names. What happens is exactly what you'd expect if you
tried to write the query without resorting to using ".*"David J.
2017-09-23 4:49 GMT+02:00 Joel Hoffman <joel.hoffman@gmail.com>:
Thanks for the response. Here's the documentation reference:
https://www.postgresql.org/docs/9.6/static/rowtypes.html#ROWTYPES-USAGE
Not a bug, but I do think it's very surprising behavior.
yes, but the fix is very simple - you just need only one level of nested
query more - or you call function from FROM clause.
The reason of this behave is given by implementation, that is very simple.
Nobody had too issues with it and nobody sent a patch to change it.
Regards
Pavel
Show quoted text
Joel
On Fri, Sep 22, 2017 at 5:22 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Friday, September 22, 2017, Joel Hoffman <joel.hoffman@gmail.com>
wrote:If I create a function with more than one out parameter, and then refer
to it inside parentheses as a record, e.g. select (function()).*, the
function appears to be evaluated multiple times, once for every column
returned. This seems to be true regardless of whether it's defined as
volatile or immutable....
As far as I can tell, this behavior has been the same since at least
version 8.2 and up through 10 beta 4, but I can't find any references to it
and it seems very surprising. It could certainly cause unexpected results
if the function has side effects. Is this a bug?It is not a bug or likely to be fixed. LATERAL makes the need for
function invocation in the manner you describe nearly unnecessary and you
can use OFFSET 0 in other cases to put the function call in a subquery and
place the (composite).* expressionin the upper-level.There is a cautionary note somewhere in docs about this. It is a parser
byproduct. The star gets expanded at parse time to individual and
independent column names. What happens is exactly what you'd expect if you
tried to write the query without resorting to using ".*"David J.