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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
