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_printtup
	CONTEXT:  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 !

Mon, 28 Jun 2004 19:54:21 +0200
Darko Prenosil <darko.prenosil@finteh.hr>
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

Mon, 28 Jun 2004 15:39:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: improper call to spi_printtup ??? 
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
>


Mon, 28 Jun 2004 22:53:32 +0200
"Darko Prenosil" <Darko.Prenosil@finteh.hr>
Re: improper call to spi_printtup ??? 
> ----- 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 !

Tue, 29 Jun 2004 09:11:45 +0200
Darko Prenosil <darko.prenosil@finteh.hr>
Re: improper call to spi_printtup ???