Executing a query and returning the result set using the SPI

Hello all,

I'm messing around with the Server Programming Interface and the
particular example presented at:

http://www.postgresql.org/docs/current/interactive/spi-examples.html 

Ideally, I would want to make the example function return the
information as a "set" and not through elog() so I can later access it
and print it using PHP.

I have a few ideas on how this can be accomplished but I haven't found
any simple example to fully elucidate me. I have been reading:

http://www.postgresql.org/docs/current/interactive/xfunc-c.html (33.7.9)

The code right now compiles fine but it crashes the server on loading:

veiculos=# CREATE FUNCTION teste(text) RETURNS setof veiculo AS
'teste.so' LANGUAGE C;
veiculos=# select * from teste('select * from veiculo');
server closed the connection unexpectedly

This is PostgreSQL version: 7.4.1

I'm compiling the code with:

$ gcc -Wall -fpic -c -I` pg_config --includedir` teste.c 
$ gcc -shared -o teste.so teste.o

Here's the code:

#include "server/postgres.h"
#include "server/fmgr.h"
#include "server/executor/spi.h"
#include "server/funcapi.h"
#include "server/access/heapam.h"
#include "server/catalog/pg_type.h"
#include "server/storage/lock.h"
#include "server/storage/proc.h"
#include "server/utils/builtins.h"

PG_FUNCTION_INFO_V1(teste);

Datum teste(PG_FUNCTION_ARGS) {
    char *command;
    int ret;
    FuncCallContext     *funcctx;
    TupleDesc            tupdesc;
    TupleTableSlot      *slot;
    AttInMetadata       *attinmeta;
    int                  call_cntr;
    int                  max_calls;
    text  *sql_command = PG_GETARG_TEXT_P(0);

    command = DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(sql_command)));    
    SPI_connect();
    
    ret = SPI_exec(command, 0);

    if (SRF_IS_FIRSTCALL()) {
	MemoryContext oldcontext;
	
	/* create a function context for cross-call persistence */
	funcctx = SRF_FIRSTCALL_INIT();
	
	/* switch to memory context appropriate for multiple function calls */
	oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
	
	/* total number of tuples to be returned */
	funcctx->max_calls = PG_GETARG_UINT32(0);
	
	/*
	 * Build a tuple description for a tuple
	 */
	tupdesc = SPI_tuptable->tupdesc;
	
	/* allocate a slot for a tuple with this tupdesc */
	slot = TupleDescGetSlot(tupdesc);
	
	/* assign slot to function context */
	funcctx->slot = slot;
	
	/*
	 * Generate attribute metadata needed later to produce tuples from raw
	 * C strings
	 */
	attinmeta = TupleDescGetAttInMetadata(tupdesc);
	funcctx->attinmeta = attinmeta;
	
	MemoryContextSwitchTo(oldcontext);
    }
    
    /* stuff done on every call of the function */
    funcctx = SRF_PERCALL_SETUP();
    
    call_cntr = funcctx->call_cntr;
    max_calls = funcctx->max_calls;
    slot = funcctx->slot;
    attinmeta = funcctx->attinmeta;
    
    if (call_cntr < max_calls) {    /* do when there is more left to
send */     	char       **values;
	HeapTuple    tuple;
	Datum        result;
	
	/*
	 * Prepare a values array for storage in our slot.
	 * This should be an array of C strings which will
	 * be processed later by the appropriate "in" functions.
	 */
	values = (char **) palloc(3 * sizeof(char *));
	values[0] = (char *) palloc(16 * sizeof(char));
	values[1] = (char *) palloc(16 * sizeof(char));
	values[2] = (char *) palloc(16 * sizeof(char));
	
	snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
	snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
	snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
	
	/* build a tuple */
	tuple = BuildTupleFromCStrings(attinmeta, values);
	
	/* make the tuple into a datum */
	result = TupleGetDatum(slot, tuple);
	
	/* Clean up (this is not actually necessary) */
	pfree(values[0]);
	pfree(values[1]);
	pfree(values[2]);
	pfree(values);
	
	SRF_RETURN_NEXT(funcctx, result);
    }
    else {    /* do when there is no more left */
	SRF_RETURN_DONE(funcctx);
    }
    
    SPI_finish();
    pfree(command);
    
}

--
Nuno Morgadinho
Undergraduate Computer Science Student
Évora University, Portugal