Re: Highly Efficient Custom Sorting

On 07/06/2010 12:42 PM, Eliot Gable wrote:
> Thanks for suggesting array_unnest(). I think that will actually prove
> more useful to me than the other example I'm using for extracting my
> data from an array. I was actually planning on computing the order on
> the first call and storing it in a linked list which gets returned one
> item at a time until all rows have been returned. Also, I found a code
> example using Google that showed someone storing data across function
> calls using that pointer. I used their example to produce this:
>
> <snip>
> if(SRF_IS_FIRSTCALL()) {
> funcctx = SRF_FIRSTCALL_INIT();
>
> /* This is where we stick or sorted data for returning later */
> funcctx->user_fctx =
> MemoryContextAlloc(funcctx->multi_call_memory_ctx, sizeof(sort_data));
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
> data = (sort_data*) funcctx->user_fctx;
> </snip>
>
> I have a structure set up that is typedef'd to "sort_data" which stores
> pointers to various things that I need to survive across the calls.
> Since this seems to be what you are suggesting, I assume this is the
> correct approach.

This approach works, but you could also use the SFRM_Materialize mode
and calculate the entire result set in one go. That tends to be simpler.
See, for example crosstab_hash() in contrib/tablefunc for an example.

FWIW, there are also some good examples of array handling in PL/R, e.g.
pg_array_get_r() in pg_conversion.c

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support