SELECT from record-returning function causes function code to be executed multiple times - Mailing list pgsql-general

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.