Postgresql c function returning one row with 2 fileds

Question

I'm new in postgresql c function and I start following examples.

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).

The function below has problem passing the check

(get_call_result_type(fcinfo, &resultTypeId, &resultTupleDesc) != TYPEFUNC_COMPOSITE)

If I remove this line I get 1 integer as result from this query

select * from pdc_imuanno(2012);

and error from

select (a).* from pdc_imuanno(2012) a;

because is not a composite type.

Question is I how I can prepare template for tuple if it's not correct this

resultTupleDesc = CreateTemplateTupleDesc(2, false);
TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "abp1", FLOAT4OID, -1, 0);
TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "abp2", FLOAT4OID, -1, 0);

And more in

get_call_result_type(fcinfo, &resultTypeId, &resultTupleDesc)

fcinfo what is and where come from?

source table:

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 <math.h>

#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 > 0 && SPI_tuptable != NULL)
  {
    HeapTuple tuple;
    tupledesc = SPI_tuptable->tupdesc;
    SPITupleTable *tuptable = SPI_tuptable;
    for (j = 0; j < proc; j++)
    {
      tuple = tuptable->vals[j];
      abp1 += DatumGetFloat4(SPI_getbinval(tuple, tupledesc, 2, &bisnull));
      abp2 += DatumGetFloat4(SPI_getbinval(tuple, tupledesc, 3, &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, &resultTypeId, &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 ) );

}

---

creation function:

CREATE FUNCTION pdc_imuanno(integer)
RETURNS float
AS 'pdc','test_query'
LANGUAGE C STABLE STRICT;
ALTER FUNCTION pdc_imuanno(integer) OWNER TO www;

query:

select * from pdc_imuanno(2012);

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.

with this creation sql it works

CREATE FUNCTION pdc_imuanno(integer)
RETURNS TABLE(abp1 real, abp2 real)
AS 'pdc','test_query'
LANGUAGE C STABLE STRICT;

Anyway it works with limited number of rows to sum and if I extend the rows number it crash in this point

rettuple = heap_form_tuple( resultTupleDesc, retvals, retnulls);

I guess there are some error in types for values

so I query on the table fields as numeric::real, I get them as float4 and I output them as datum.

Where is my error?

Thanks a lot for any help.

This in my first post in StackOverflow.

Luca

Answer 1

in:

get_call_result_type(fcinfo, &resultTypeId, &resultTupleDesc)

fcinfo - what is and where come from?

fcinfo is part of PG_FUNCTION_ARGS in the v1 calling convention. It's the context of the function call and contains all sorts of details like parameters, etc. Much of this is handled behind the scenes by the GETARG macros, etc, but you need to pass it around to helper functions.

The rest of the question is hard to understand because of the typos etc. I'm guessing that you want to write a function in C to add two values together and return the result? If so, that doesn't make much sense to return as two fields in a composite type. Please show the relevant CREATE TYPE statements, the CREATE OR REPLACE FUNCTION declaration you used to define the function, and explain what you are trying to do and why.

(If you edit your question, post a comment in reply to this answer otherwise or I won't know you edited.)