return a set of records

Started by Werner Echezuriaover 16 years ago3 messages
#1Werner Echezuria
wercool@gmail.com

Hi, I need to return a set of records from a query, first I translate
from sqlf to sql and later I wanna return the query, but the server
crash (I guess it crashes around the yyparse call).

This is the sql:

CREATE OR REPLACE FUNCTION sqlf (text) RETURNS SETOF record
AS 'MODULE_PATHNAME', 'sqlf'
LANGUAGE C IMMUTABLE STRICT;

This is the function:

Datum sqlf(PG_FUNCTION_ARGS) {

char *query = TextDatumGetCString(PG_GETARG_DATUM(0));
void *result;
int               ret,proc;
int j,i;

FuncCallContext *funcctx;
int call_cntr;
int max_calls;

TupleDesc tupdesc;
SPITupleTable *tuptable;
AttInMetadata *attinmeta;

yy_scan_string(query);
sqlf_yyparse(&result);

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

  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;
HeapTuple tuple;
Datum datum_result;
bool isnull;

values = (Datum **) palloc(tupdesc->natts * sizeof(Datum *));

for (i = 1; i <= tupdesc->natts; i++)
values[i]=SPI_getbinval(tuple,tupdesc,i,&isnull);

tuple=tuptable->vals[j];

// 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

SRF_RETURN_DONE(funcctx);
}

}

Greetings.

#2Andrew Dunstan
andrew@dunslane.net
In reply to: Werner Echezuria (#1)
Re: return a set of records

t

Werner Echezuria wrote:

Hi, I need to return a set of records from a query, first I translate
from sqlf to sql and later I wanna return the query, but the server
crash (I guess it crashes around the yyparse call).

This is the sql:

CREATE OR REPLACE FUNCTION sqlf (text) RETURNS SETOF record
AS 'MODULE_PATHNAME', 'sqlf'
LANGUAGE C IMMUTABLE STRICT;

You function doesn't look too immutable. Is it really?

cheers

andrew

#3Werner Echezuria
wercool@gmail.com
In reply to: Andrew Dunstan (#2)
Re: return a set of records

2009/8/28 Andrew Dunstan <andrew@dunslane.net>:

You function doesn't look too immutable. Is it really?

Hi, I fixed that, but the server continues to crash, where can I see a
full example of something using the SRF functions to parse a query?
All examples I see set the columns, but I parse a query that I don't
have any information about attrs.

thanks