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