From: | Werner Echezuria <wercool(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | C function doesn't return more than one tuple |
Date: | 2009-08-28 19:50:40 |
Message-ID: | 2485a25e0908281250m1b329dc0y262a182f860f8d30@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi, I'm trying to develop a C module, but when the query result is
more than one tuple the server crashes.
Here is the code:
#include "postgres.h"
#include "gram.h"
#include "utils/builtins.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "access/heapam.h"
#include "fmgr.h"
extern Datum sqlf(PG_FUNCTION_ARGS);
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(sqlf);
Datum
sqlf(PG_FUNCTION_ARGS)
{
char *query = TextDatumGetCString(PG_GETARG_DATUM(0));
const char *result="SELECT name FROM people WHERE age>10";
int ret,proc;
int j,i;
FuncCallContext *funcctx;
int call_cntr;
int max_calls;
TupleDesc tupdesc;
TupleDesc tupledesc;
SPITupleTable *tuptable;
AttInMetadata *attinmeta;
// stuff done only on the first call of the function
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);
SPI_connect();
ret=SPI_execute(result,true,0);
proc=SPI_processed;
// total number of tuples to be returned
funcctx->max_calls = proc;
if (ret > 0 && SPI_tuptable != NULL){
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
}
tupledesc = BlessTupleDesc(tupdesc);
MemoryContextSwitchTo(oldcontext);
}
// stuff done on every call of the function
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx->call_cntr;
max_calls = funcctx->max_calls;
attinmeta = funcctx->attinmeta;
j=0;
if (call_cntr < max_calls) { // do when there is more left to send
Datum values[tupdesc->natts];
bool nulls[tupdesc->natts];
HeapTuple tuple;
Datum datum_result;
bool isnull;
tuple=tuptable->vals[j];
for (i = 0; i < tupdesc->natts; i++){
values[i]=SPI_getbinval(tuple,tupledesc,i+1,&isnull);
nulls[i]=false;
}
// build a tuple
tuple= heap_form_tuple(tupledesc,values,nulls);
// make the tuple into a datum
datum_result = HeapTupleGetDatum(tuple);
j++;
SRF_RETURN_NEXT(funcctx, datum_result);
} else { // do when there is no more left
SPI_finish();
SRF_RETURN_DONE(funcctx);
}
}
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-28 22:06:40 | Re: High load on commit after important schema changes |
Previous Message | Sergey Samokhin | 2009-08-28 19:44:21 | Re: A safe way to upgrade table definitions by using ALTER's |