C function doesn't return more than one tuple

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);
}

}

Browse pgsql-general by date

  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