title: Postgresql c function returning one row with 2 fileds
body: <p>I'm new in postgresql c function and I start following examples.</p>
  
  <p>I want to write a simple function that has inside an SQL and receiving parameter evaluete and return 2 fields as sum of 2 columns, separately (for now to be simpler).</p>
  
  <p>The function below has problem passing the check</p>
  
  <pre><code>(get_call_result_type(fcinfo, &amp;resultTypeId, &amp;resultTupleDesc) != TYPEFUNC_COMPOSITE)
  </code></pre>
  
  <p>If I remove this line I get 1 integer as result  from this query</p>
  
  <pre><code>select * from pdc_imuanno(2012);
  </code></pre>
  
  <p>and error from </p>
  
  <pre><code>select (a).* from pdc_imuanno(2012) a;
  </code></pre>
  
  <p>because is not a composite type.</p>
  
  <p>Question is I how I can prepare template for tuple if it's not correct this </p>
  
  <pre><code>resultTupleDesc = CreateTemplateTupleDesc(2, false);
  TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "abp1", FLOAT4OID, -1, 0);
  TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "abp2", FLOAT4OID, -1, 0);
  </code></pre>
  
  <p>And more in </p>
  
  <pre><code>get_call_result_type(fcinfo, &amp;resultTypeId, &amp;resultTupleDesc)
  </code></pre>
  
  <p>fcinfo what is and where come from?</p>
  
  <p>source table: </p>
  
  <pre><code>CREATE TABLE imu.calcolo (
    codfis character varying(16) NOT NULL,
    anno integer NOT NULL,
    abp1 numeric,
    abp2 numeric,
    CONSTRAINT imucalcolo_pkey PRIMARY KEY (codfis, anno)
  )
  WITH ( OIDS=FALSE );
  -------------------------------------------------------
  #include "postgres.h"
  #include "fmgr.h"
  
  #include "catalog/pg_type.h"
  #include "funcapi.h"
  #include "executor/spi.h"
  #include "lib/stringinfo.h"
  #include "miscadmin.h"
  
  #include &lt;math.h&gt;
  
  #include "utils/builtins.h"
  #include "utils/guc.h"
  #include "utils/lsyscache.h"
  #include "utils/memutils.h"
  #include "utils/numeric.h"
  
  #include "access/htup_details.h"
  
  #ifdef PG_MODULE_MAGIC
  PG_MODULE_MAGIC;
  #endif
  
  PG_FUNCTION_INFO_V1(test_query);
  Datum test_query(PG_FUNCTION_ARGS);
  
  Datum
  test_query(PG_FUNCTION_ARGS)
  {
    TupleDesc resultTupleDesc, tupledesc;
    bool bisnull, cisnull;
    Oid resultTypeId;
    Datum retvals[2];
    bool retnulls[2];
    HeapTuple rettuple;
  
    sprintf(query,"SELECT anno, abp1::real, abp2::real "
                  "FROM imu.calcolo WHERE anno = %d;",PG_GETARG_INT32(0));
  
    int ret;
    int proc;
    float abp1 = 0;
    float abp2 = 0;
    SPI_connect();
    ret = SPI_exec(query,0);
    proc = SPI_processed;
  
    if (ret &gt; 0 &amp;&amp; SPI_tuptable != NULL)
    {
      HeapTuple tuple;
      tupledesc = SPI_tuptable-&gt;tupdesc;
      SPITupleTable *tuptable = SPI_tuptable;
      for (j = 0; j &lt; proc; j++)
      {
        tuple = tuptable-&gt;vals[j];
        abp1 += DatumGetFloat4(SPI_getbinval(tuple, tupledesc, 2, &amp;bisnull));
        abp2 += DatumGetFloat4(SPI_getbinval(tuple, tupledesc, 3, &amp;cisnull));
      }
    }
    resultTupleDesc = CreateTemplateTupleDesc(2, false);
    TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "abp1", FLOAT4OID, -1, 0);
    TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "abp2", FLOAT4OID, -1, 0);
  
    if (get_call_result_type(fcinfo, &amp;resultTypeId, &amp;resultTupleDesc) != TYPEFUNC_COMPOSITE) {
      ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                      errmsg("function returning record called in context that cannot accept type record")));
    }
    resultTupleDesc = BlessTupleDesc(resultTupleDesc);
    SPI_finish();
    retvals[0] = Float4GetDatum(abp1);
    retvals[1] = Float4GetDatum(abp2);
    retnulls[1] = bisnull;
    retnulls[2] = cisnull;
    rettuple = heap_form_tuple( resultTupleDesc, retvals, retnulls);
    PG_RETURN_DATUM( HeapTupleGetDatum( rettuple ) );
  
  }
  </code></pre>
  
  <hr>
  
  <p>creation function:</p>
  
  <pre><code>CREATE FUNCTION pdc_imuanno(integer)
  RETURNS float
  AS 'pdc','test_query'
  LANGUAGE C STABLE STRICT;
  ALTER FUNCTION pdc_imuanno(integer) OWNER TO www;
  </code></pre>
  
  <p>query:</p>
  
  <pre><code>select * from pdc_imuanno(2012);
  </code></pre>
  
  <p>Ok I find the simple and silly error
  I create the function retunrning 1 field by myself and I expeted to see as return 2 fields.</p>
  
  <p>with this creation sql it works </p>
  
  <pre><code>CREATE FUNCTION pdc_imuanno(integer)
  RETURNS TABLE(abp1 real, abp2 real)
  AS 'pdc','test_query'
  LANGUAGE C STABLE STRICT;
  </code></pre>
  
  <p>Anyway it works with limited number of rows to sum and if I extend the rows number it crash
  in this point</p>
  
  <pre><code>rettuple = heap_form_tuple( resultTupleDesc, retvals, retnulls);
  </code></pre>
  
  <p>I guess there are some error in types for values</p>
  
  <p>so I query on the table fields as numeric::real, I get them as float4 and I output them as datum.</p>
  
  <p>Where is my error? </p>
  
  <p>Thanks a lot for any help.</p>
  
  <p>This in my first post in StackOverflow.</p>
  
  <p>Luca</p>