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