SELECT from record-returning function causes function code to be executed multiple times

[dbaston <dbaston@xxxxxxxxx>]

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general