Thread: return a set of records

return a set of records

From
Werner Echezuria
Date:
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.


Re: return a set of records

From
Andrew Dunstan
Date:
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


Re: return a set of records

From
Werner Echezuria
Date:
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