Set Returning C-Function with cache over multiple calls (with different arguments)

[Thilo Schneider <Thilo.Schneider@xxxxxxxxxxxxxxxxxxx>]

Dear list,

Currently I am working on a user C-Function which should create a cache object on the first call and afterwards return a set of computed values for each argument combination it is called with.

My Problem is how to get the cache object saved over multiple calls. Without the SRF I could use fcinfo->flinfo->fn_extra for my pointer to the data. This is now used by the FuncCallContext structure. This structure is destroyed every time SRF_RETURN_DONE is called, thus user_fctx also is not the way to go.

As a minimal example look at the function provided below.

--------------------------------------------------- snip ---------------------------------------------------
PG_FUNCTION_INFO_V1(test);
Datum test(PG_FUNCTION_ARGS) 
{
	MemoryContext old_context;
	FuncCallContext     *funcctx;
	
	
	if (SRF_IS_FIRSTCALL()) {
		funcctx = SRF_FIRSTCALL_INIT();

		// This is the structure potentially generated in previous calls
		str = funcctx->user_fctx;
	
		// If the structure does not exist or the geometry array has changed, it has to be created.
		if ( ! str) {
			elog(NOTICE, "create new");
		
			old_context = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
			
			// Fill str with data around here ...
			
			MemoryContextSwitchTo(old_context);
		
			funcctx->user_fctx = str;
		}
	
	}
	funcctx = SRF_PERCALL_SETUP();
	SRF_RETURN_DONE(funcctx); 
}
--------------------------------------------------- snip ---------------------------------------------------

To make the problem perfectly clear the SQL-Code this should work with:

--------------------------------------------------- snip ---------------------------------------------------
CREATE OR REPLACE FUNCTION test(int, int[] )
  RETURNS SETOF int AS 'myfunc', 'test'
  LANGUAGE 'c' IMMUTABLE STRICT
  COST 1;

SELECT test(number, array(SELECT integers FROM another_table)) FROM numbers;
--------------------------------------------------- snip ---------------------------------------------------

As creating the cache object is by far the most expensive part in the desired function, it should be possible to create the cache only once over the whole query - using only the arguments in the array, which do not change over multiple calls.

Is there a way around this problem? Another pointer I could use and do not know of yet?

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