Strange behaviour in postgresql c custom function

Question

After I spent a lot of hours to develop my first simple c custom function below I discover a strange behaviour, could someone suggests why?

If I remove this line the function is normally compiled but

>>>   elog(INFO, "ROW restituite: %d", SPI_processed);

psql:query.sql:30: connection to server was lost immediately without result.

And a second question please. Compiling I receve the warning

warning: format ‘%s’ expects argument of type ‘char *’, but argument 3 has type ‘struct VarChar *’ [-Wformat]

related to line

"ORDER BY idot;", args[0], DatumGetInt32(args[1]));

But no way to cast it to integer

not with DatumGetVarCharP nor DatumGetTextP

and without VARDATA here args[0] = VARDATA(PG_GETARG_VARCHAR_P(0)); I get another error

Thanks a lot

luca

CREATE FUNCTION imu.posot_cf_anno(varchar,integer)
RETURNS TABLE(idot integer, validita integer)
AS 'pdc','posot_cf_anno'
LANGUAGE C STABLE STRICT;
// ------------------------------------------

#include "postgres.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "catalog/pg_type.h"


PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(posot_cf_anno);

Datum posot_cf_anno(PG_FUNCTION_ARGS);

struct pos_idot {
  int32 idot;
  int32 validita;
  bool argnulls[2];
  bool anyargnull;
};

Datum posot_cf_anno(PG_FUNCTION_ARGS) {
  int ret;
  char query[1024];
  Datum args[2];

  struct pos_idot *rinargs;
  struct pos_idot *rowsinargs[SPI_processed]; // creo un array di pos_idot

  SPI_connect();
  args[0] = VARDATA(PG_GETARG_VARCHAR_P(0));
  args[1] = PG_GETARG_INT32(1);

  sprintf(query,"SELECT DISTINCT idot, validita FROM sit.otpos "
          "WHERE btrim(codfis) = '%s' AND "
          "date_part('year',to_timestamp(validita::double precision)) <= "
          "date_part('year',to_timestamp(%d||'-01-01','YYYY-MM-DD')) "
          "ORDER BY idot;", args[0], DatumGetInt32(args[1]));
  ret = SPI_exec(query, 0);
  elog(INFO, "ROW restituite: %d", SPI_processed);

  if (ret > 0 && SPI_tuptable != NULL) {

    int i, j, call_nr;
    for (j = 0; j < SPI_processed; j++)
    {
      rinargs = palloc0(sizeof(struct pos_idot));
      rinargs->idot = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[j], SPI_tuptable->tupdesc, 1, &rinargs->argnulls[0]));
      rinargs->validita = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[j], SPI_tuptable->tupdesc, 2, &rinargs->argnulls[1]));
      if (rinargs->argnulls[0] || rinargs->argnulls[1]) rinargs->anyargnull = true;
      rowsinargs[j] = rinargs;
    }

    FuncCallContext *funcctx;
    struct pos_idot *rargs;
    struct pos_idot *rowsargs[SPI_processed]; // creo un array di pos_idot

    if (SRF_IS_FIRSTCALL())
    {
      funcctx = SRF_FIRSTCALL_INIT();
      MemoryContext oldcontext;
      oldcontext = MemoryContextSwitchTo( funcctx->multi_call_memory_ctx );

      rargs = palloc0(sizeof(struct pos_idot));
      rargs->anyargnull = false;
      funcctx->user_fctx = rargs;
      funcctx->max_calls = SPI_processed; // there are 6 permutations of 3 elements

      rargs->idot = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &rargs->argnulls[0]));
      rargs->validita = DatumGetInt32(SPI_getbinval(SPI_tuptable->vals[1], SPI_tuptable->tupdesc, 2, &rargs->argnulls[1]));
      if (rargs->argnulls[0] || rargs->argnulls[1]) rargs->anyargnull = true;

      if (get_call_result_type(fcinfo, NULL, &funcctx->tuple_desc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR,
          (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
           errmsg("function returning record called in context that cannot accept type record")));

      BlessTupleDesc(funcctx->tuple_desc);

      MemoryContextSwitchTo(oldcontext);
    }
    funcctx = SRF_PERCALL_SETUP();
    rargs = funcctx->user_fctx;
    call_nr = funcctx->call_cntr;

    if (call_nr < funcctx->max_calls) {
      HeapTuple rettuple;
      Datum retvals[2];
      bool retnulls[2];
      retvals[0] = Int32GetDatum(rowsinargs[call_nr]->idot); // idot
      retnulls[0] = rowsinargs[call_nr]->argnulls[0]; // idot null
      retvals[1] = Int32GetDatum(rowsinargs[call_nr]->validita); // validita
      retnulls[1] = rowsinargs[call_nr]->argnulls[1]; // validita null

      rettuple = heap_form_tuple(funcctx->tuple_desc, retvals, retnulls);
      SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum( rettuple ));
      }
      else /* do when there is no more left */
    {
      SPI_finish();
      SRF_RETURN_DONE(funcctx);
    }
  }
  return 0;
}

Pavel Stehule · Accepted Answer · 2013-11-20 13:15:26Z

1

I am sure so

struct pos_idot *rowsinargs[SPI_processed]; // creo un array di pos_idot

is wrong, and logic probably wrong too. You would to execute query only once, so you have to push query execution to SRF_IS_FIRSTCALL() path.

args[0] = VARDATA(PG_GETARG_VARCHAR_P(0));

is bad too. Varchar is not zero terminated string! So you can't to use it in sprintf directly. There are nice macro text_to_cstring. sprintf know nothing about PostgreSQL internal types - you should to translate parameters to C types.

sprintf(query,"SELECT DISTINCT idot, validita FROM sit.otpos "
          "WHERE btrim(codfis) = '%s' AND "
          "date_part('year',to_timestamp(validita::double precision)) <= "
          "date_part('year',to_timestamp(%d||'-01-01','YYYY-MM-DD')) "
          "ORDER BY idot;", args[0], DatumGetInt32(args[1]));

should be

sprintf(query,"SELECT DISTINCT idot, validita FROM sit.otpos "
          "WHERE btrim(codfis) = '%s' AND "
          "date_part('year',to_timestamp(validita::double precision)) <= "
          "date_part('year',to_timestamp(%d||'-01-01','YYYY-MM-DD')) "
          "ORDER BY idot;", text_to_cstring(PG_GETARG_TEXT_PP(0)), DatumGetInt32(args[1]));

Dynamic array allocation

#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int x;
    int y;
} mystruct;

void main()
{
   mystruct **array;

   /* inside pg use palloc instead malloc */
   array = malloc(sizeof(mystruct*) * 10);
   array[0] = malloc(sizeof(mystruct));
   array[0]->x = 10;
   array[0]->y = 20;
}

edited Nov 20, 2013 at 13:15

answered Nov 19, 2013 at 17:12

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Luca Marletta Over a year ago

Thanks Pavel; may be I need int proc; and proc = SPI_processed; struct pos_idot *rowsinargs[proc]; is it?

Pavel Stehule Over a year ago

SPI_processed has value after execution (so you cannot use it in declaration). There should be only numeric constant. You don't know what is array size before, so you can use repeatedly resized array pattern or some form linked list or ... and now I see so all logic is bad - probably you would to exec query only for first call.

Luca Marletta Over a year ago

Exactly just for the first call. Thanks a lot!

Luca Marletta Over a year ago

Hi Pavel, How do you inizialize and array of struct and set alfer the size? I mean I need to inizialize this struct pos_idot *rowsinargs;// [SPI_processed]; and set the correct size after perform the query and have SPI_processed. How do you do it?

Pavel Stehule Over a year ago

@LucaMarletta no, you should to wait for valid SPI_processed and then you can create array dynamically - see updated answer.

|

Collectives™ on Stack Overflow

Strange behaviour in postgresql c custom function

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related