Обсуждение: SELECT from record-returning function causes function code to be executed multiple times

Hello,

I have a simple C-language function , SimpleSRF(), that returns RECORDs with
two integer columns.  When I call the function using SELECT (SIMPLESRF()).*,
the function code is being executed twice.  I'm wondering if this is
expected behavior?

The function is defined as:

CREATE OR REPLACE FUNCTION SIMPLESRF(OUT id integer, OUT type integer)
RETURNS SETOF RECORD
AS '$libdir/pg1.dll', 'simplesrf'
LANGUAGE C IMMUTABLE STRICT;

The function follows the basic structure for an SRF in the docs.  I included
some output statements in the function like this so that I could keep track
of the repeated calls.

elog(INFO, "Call %i", funcctx->call_cntr);

Calling the function like this: SELECT (SIMPLESRF()).* gives me the
following messages:

INFO:  Call 0
INFO:  Call 0
INFO:  Call 1
INFO:  Call 1
INFO:  Call 2
INFO:  Call 2
INFO:  Call 3
INFO:  Call 3
INFO:  Call 4
INFO:  Call 4
INFO:  Call 5
INFO:  Call 5

Total query runtime: 18 ms.
5 rows retrieved.

Apparently the function code is being executed twice, once for each column
in the return tuple.

Alternatively, I can call the function like this:  SELECT * FROM SIMPLESRF()
This gives me the messages I'd expect:

INFO:  Call 0
INFO:  Call 1
INFO:  Call 2
INFO:  Call 3
INFO:  Call 4
INFO:  Call 5

Total query runtime: 12 ms.
5 rows retrieved.

Is this expected behavior?  I would think that the function would only need
to be executed once with either calling pattern, especially since it's been
declared as IMMUTABLE.

I do get different plans with the two calling patterns, though I don't know
enough to understand the differences.

EXPLAIN SELECT (SIMPLESRF()).*
"Result  (cost=0.00..5.01 rows=1000 width=0)"

EXPLAIN SELECT * FROM SIMPLESRF()
"Function Scan on simplesrf  (cost=0.00..10.00 rows=1000 width=8)"

Function code, though I don't think it's relevant:

PG_FUNCTION_INFO_V1(simplesrf);
__declspec(dllexport) Datum simplesrf(PG_FUNCTION_ARGS) {
    FuncCallContext *funcctx;
    TupleDesc resultTupleDesc;
    HeapTuple returnTuple;
    Datum returnValues[2];
    bool returnNulls[2] = {false};

    if (SRF_IS_FIRSTCALL()) {
        struct vertex** a;
        MemoryContext oldContext;
        funcctx = SRF_FIRSTCALL_INIT();
        oldContext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        a = (struct vertex**) palloc(4 * sizeof(struct vertex*));

        resultTupleDesc = CreateTemplateTupleDesc(2, false);
        TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "call", INT4OID, -1,
0);
        TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "call2", INT4OID, -1,
0);
        resultTupleDesc = BlessTupleDesc(resultTupleDesc);
        funcctx->tuple_desc = resultTupleDesc;
        funcctx->max_calls = 5;

        MemoryContextSwitchTo(oldContext);
    }

    funcctx = SRF_PERCALL_SETUP();
    resultTupleDesc = funcctx->tuple_desc;
    elog(INFO, "Call %i", funcctx->call_cntr);

    if (funcctx->call_cntr < funcctx->max_calls) {
        returnValues[0] = Int32GetDatum(1);
        returnValues[1] = Int32GetDatum(2);
        returnTuple = heap_form_tuple(resultTupleDesc, returnValues, returnNulls);

        SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(returnTuple));
    } else {
        SRF_RETURN_DONE(funcctx);
    }
}



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SELECT-from-record-returning-function-causes-function-code-to-be-executed-multiple-times-tp5783495.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

dbaston wrote
> I'm wondering if this is expected behavior?

Yes.

The proper way to handle this is by putting the SRF in the FROM clause.

If you must have it in the select clause you should do this:

WITH srf_call (
SELECT srf_function() AS srf_result
)
SELECT (srf_call.srf_result).* FROM srf_call;

Note that if you are using 9.3 you likely can make use of the new LATERAL
construct to leave the SRF call in the FROM clause while still pulling
parameter values from the same level in the query (which is the main reason
for moving the SRF to the select-list).

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SELECT-from-record-returning-function-causes-function-code-to-be-executed-multiple-times-tp5783495p5783497.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.