Hello all,
I'm messing around with the Server Programming Interface and the
particular example presented at:
http://www.postgresql.org/docs/curre...-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/curre...e/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_INF O_V1(teste);
Datum teste(PG_FUNCTI ON_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 (DirectFunction Call1(textout,
PointerGetDatum (sql_command))) ;
SPI_connect();
ret = SPI_exec(comman d, 0);
if (SRF_IS_FIRSTCA LL()) {
MemoryContext oldcontext;
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_I NIT();
/* switch to memory context appropriate for multiple function calls */
oldcontext = MemoryContextSw itchTo(funcctx->multi_call_mem ory_ctx);
/* total number of tuples to be returned */
funcctx->max_calls = PG_GETARG_UINT3 2(0);
/*
* Build a tuple description for a tuple
*/
tupdesc = SPI_tuptable->tupdesc;
/* allocate a slot for a tuple with this tupdesc */
slot = TupleDescGetSlo t(tupdesc);
/* assign slot to function context */
funcctx->slot = slot;
/*
* Generate attribute metadata needed later to produce tuples from raw
* C strings
*/
attinmeta = TupleDescGetAtt InMetadata(tupd esc);
funcctx->attinmeta = attinmeta;
MemoryContextSw itchTo(oldconte xt);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SET UP();
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 = BuildTupleFromC Strings(attinme ta, values);
/* make the tuple into a datum */
result = TupleGetDatum(s lot, 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
