Thread: improper call to spi_printtup ???

I have set returning function written in 'c', declared as:

CREATE OR REPLACE FUNCTION check_view (text,text) RETURNS setof pg_attribute AS
'/usr/local/pgsql/lib/libplpq.so','check_view'LANGUAGE 'c' WITH (isstrict);
 

When I call this function from psql :SELECT attrelid,attnum FROM check_view('pg_catalog','pg_tables') ;
I have: attrelid | attnum----------+--------    16595 |      1     1259 |      1        0 |      0     1259 |     11
1259 |     22        0 |      0
 
That is expected result, or in other words it works fine.

Now when I try to use 'check_view0 function in some other pl/psql function:
CREATE OR REPLACE FUNCTION testfunc() RETURNS bool AS 'BEGIN    SELECT attrelid,attnum FROM
check_view(''pg_catalog'',''pg_tables'');    RETURN FALSE;END;' LANGUAGE 'plpgsql';
 
SELECT testfunc() ;

I have:(-403)ERROR:  improper call to spi_printtupCONTEXT:  PL/pgSQL function "testfunc" line 2 at SQL statement

Anyone knows what I'm doing wrong ? Is there some special issue when writing 
functions for pl/psql that I'm not aware ?

Thanks in advance !

Darko Prenosil <darko.prenosil@finteh.hr> writes:
> Anyone knows what I'm doing wrong ?

Well, when you didn't show us the text of the function, no.

However, a reasonable bet would be that you used SPI inside the function
and did not use it correctly, leaving the SPI state corrupted when
control got back to plpgsql.
        regards, tom lane

Here is the source, but now when You mentioned SPI state, I see that I put
SPI_finish stupidly after SRF_RETURN_NEXT(funcctx, result);
Could that be my problem  ?

Regards !

PG_FUNCTION_INFO_V1(check_view);
Datum
check_view(PG_FUNCTION_ARGS)
{FuncCallContext *funcctx;MemoryContext oldcontext;int spiRet=0;PQExpBuffer queryBuff;void *plan=0;   char *schemaName
=0;   char *viewName = 0;int ret = -1;
 
uint   call_cntr;uint   max_calls;TupleTableSlot *slot;AttInMetadata *attinmeta;
typedef struct sqlPlanInfo{ SPITupleTable* result; List* targetList;} sqlPlanInfo;
if ( SRF_IS_FIRSTCALL()){
 schemaName = GET_STR(PG_GETARG_TEXT_P(0)); viewName = GET_STR(PG_GETARG_TEXT_P(1)); queryBuff = createPQExpBuffer();
 if (schemaName == NULL)  elog(ERROR, "schemaName not set");
 if (viewName == NULL)  elog(ERROR, "viewName not set");
 if (_SPI_connected >=0){   elog(NOTICE, "ALREADY CONNECTED");  spiRet = _SPI_connected; }else{  if ((spiRet =
SPI_connect())< 0)   elog(ERROR, "rlog: SPI_connect returned %d", spiRet); }
 
 funcctx = SRF_FIRSTCALL_INIT(); oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
 printfPQExpBuffer(queryBuff,  "SELECT definition"  " FROM pg_views WHERE schemaname='%s' "  " AND viewname
='%s';",schemaName,viewName);
 
 ret = SPI_exec(queryBuff->data,1); if (ret == SPI_OK_SELECT){  if ( SPI_processed > 0 ){   TupleDesc tupdesc =
SPI_tuptable->tupdesc;   printfPQExpBuffer(     queryBuff,     "%s",     SPI_getvalue(SPI_tuptable->vals[0],tupdesc,1)
 );  }else{   elog(ERROR, "Unexisting view %s.%s", schemaName,viewName );  } }else{  elog(ERROR, "Error executing %s",
queryBuff->data); }
 
 plan = SPI_prepare(queryBuff->data, 0, 0); if (!plan)  elog(ERROR, "Unable to create plan for %s", queryBuff->data );
 ret = SPI_execp(plan,0, 0, 0);
 if (ret < 0){  elog(ERROR, "Error executing %s", queryBuff->data ); }else{  List
*raw_parsetree_list=pg_parse_query(queryBuff->data); Node *parsetree = (Node *) lfirst(raw_parsetree_list);  List
*query_list= pg_analyze_and_rewrite(parsetree,0,0);  Query *queryTree = (Query *) lfirst(query_list);  sqlPlanInfo* inf
=(sqlPlanInfo*) palloc(sizeof(sqlPlanInfo));
 
  inf->result = SPI_tuptable;  inf->targetList = queryTree->targetList;  funcctx->max_calls =
inf->result->tupdesc->natts;
  /*  * Generate attribute metadata needed later to produce tuples  */  TupleDesc tupdescRes;  tupdescRes =
CreateTemplateTupleDesc(18,false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 1, "attrelid",OIDOID, -1, 0,
 
false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 2, "attname",NAMEOID, -1, 0,
false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 3, "atttypid",OIDOID, -1, 0,
false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 4, "attstattarget",
INT4OID, -1, 0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 5, "attlen",INT2OID, -1, 0,
false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 6, "attnum",INT2OID, -1, 0,
false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 7, "attndims",INT4OID, -1, 0,
false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 8, "attcacheoff",INT4OID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 9, "atttypmod", INT4OID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 10, "attbyval",BOOLOID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 11, "attstorage",CHAROID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 12, "attisset", BOOLOID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 13, "attalign",CHAROID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 14, "attnotnull",BOOLOID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 15, "atthasdef", BOOLOID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 16,
"attisdropped",BOOLOID, -1, 0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 17, "attislocal",BOOLOID, -1,
0, false);  TupleDescInitEntry(tupdescRes, (AttrNumber) 18,
"attinhcount",INT4OID, -1, 0, false);
  slot = TupleDescGetSlot(tupdescRes);  funcctx->slot = slot;  attinmeta = TupleDescGetAttInMetadata(tupdescRes);
funcctx->attinmeta= attinmeta;  funcctx->user_fctx = inf; }
 
 MemoryContextSwitchTo(oldcontext);}
funcctx = SRF_PERCALL_SETUP();call_cntr = funcctx->call_cntr;max_calls = funcctx->max_calls;slot =
funcctx->slot;sqlPlanInfo*inf = (sqlPlanInfo*)funcctx->user_fctx;
 
if (call_cntr >= funcctx->max_calls) SRF_RETURN_DONE(funcctx);
/* Do we have a non-resjunk tlist item? */while (inf->targetList && ((TargetEntry *)
lfirst(inf->targetList))->resdom->resjunk) inf->targetList = lnext(inf->targetList);
char   **values;HeapTuple tuple;Datum  result;values = (char **) palloc(18 * sizeof(char *));
if (inf->targetList){ Resdom     *res = ((TargetEntry *) lfirst(inf->targetList))->resdom;
inf->result->tupdesc->attrs[call_cntr]->attrelid= res->resorigtbl; inf->result->tupdesc->attrs[call_cntr]->attnum =
res->resorigcol;inf->targetList = lnext(inf->targetList);}
 
int cols=0;for (cols=0; cols<18;cols++){ values[cols] = (char *) palloc(256);}
sprintf(values[0], "%i", inf->result->tupdesc->attrs[call_cntr]->attrelid);sprintf(values[1], "%s",

inf->result->tupdesc->attrs[call_cntr]->attname.data);sprintf(values[2],"%i",inf->result->tupdesc->attrs[call_cntr]->atttypid);

sprintf(values[3],"%i",inf->result->tupdesc->attrs[call_cntr]->attstattarget

);sprintf(values[4],"%i",inf->result->tupdesc->attrs[call_cntr]->attlen);sprintf(values[5],"%i",inf->result->tupdesc->attrs[call_cntr]->attnum);sprintf(values[6],"%i",inf->result->tupdesc->attrs[call_cntr]->attndims);


sprintf(values[7],"%i",inf->result->tupdesc->attrs[call_cntr]->attcacheoff);sprintf(values[8],"%i",inf->result->tupdesc->attrs[call_cntr]->atttypmod);sprintf(values[9],"%i",inf->result->tupdesc->attrs[call_cntr]->attbyval);


sprintf(values[10],"%c",inf->result->tupdesc->attrs[call_cntr]->attstorage);sprintf(values[11],"%i",inf->result->tupdesc->attrs[call_cntr]->attisset);sprintf(values[12],"%c",inf->result->tupdesc->attrs[call_cntr]->attalign);


sprintf(values[13],"%i",inf->result->tupdesc->attrs[call_cntr]->attnotnull);sprintf(values[14],"%i",inf->result->tupdesc->attrs[call_cntr]->atthasdef);

sprintf(values[15],"%i",inf->result->tupdesc->attrs[call_cntr]->attisdropped
);

sprintf(values[16],"%i",inf->result->tupdesc->attrs[call_cntr]->attislocal);

sprintf(values[17],"%i",inf->result->tupdesc->attrs[call_cntr]->attinhcount)
;
/* build a tuple */tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);/* make the tuple into a datum */result =
TupleGetDatum(slot,tuple);SRF_RETURN_NEXT(funcctx, result);
 
SPI_finish();
}


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Darko Prenosil" <darko.prenosil@finteh.hr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, June 28, 2004 9:39 PM
Subject: Re: [HACKERS] improper call to spi_printtup ???


> Darko Prenosil <darko.prenosil@finteh.hr> writes:
> > Anyone knows what I'm doing wrong ?
>
> Well, when you didn't show us the text of the function, no.
>
> However, a reasonable bet would be that you used SPI inside the function
> and did not use it correctly, leaving the SPI state corrupted when
> control got back to plpgsql.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Darko Prenosil" <darko.prenosil@finteh.hr>
> Cc: <pgsql-hackers@postgresql.org>
> Sent: Monday, June 28, 2004 9:39 PM
> Subject: Re: [HACKERS] improper call to spi_printtup ???
>
> > Darko Prenosil <darko.prenosil@finteh.hr> writes:
> > > Anyone knows what I'm doing wrong ?
> >
> > Well, when you didn't show us the text of the function, no.
> >
> > However, a reasonable bet would be that you used SPI inside the function
> > and did not use it correctly, leaving the SPI state corrupted when
> > control got back to plpgsql.
> >

You figure it out right, SPI_finish was in the wrong place. 
Thanks again.

Regards !