PostgreSQL C function suggestion

Question

could some one give me an hint in this starting tentative for a custom function?

I need to construct the query with 2 parameters, a varchar and a unix timestamp (an integer) I've spent 3 hours with the few lines below and this result

a query test could be select * from pdc_posot_cf_anno('MRDDCU83C12C433F',2012); I just would like to construct a correct SQL query to pass to SPI_exec.

Thanks a lot.

CREATE FUNCTION pdc_posot_cf_anno(varchar,integer)
RETURNS integer
AS 'pdc','posot_cf_anno'
LANGUAGE C STABLE STRICT;

Datum
posot_cf_anno(PG_FUNCTION_ARGS) {
  char timestring[1024] = "";
  char qanno[1024];
  Timestamp t;
  time_t time = 0;
  int tempo;
  Datum td;
  sprintf(timestring,"%d-01-01",PG_GETARG_INT32(1));
  elog(INFO, "tutto bene %s !",timestring);
  t = DatumGetTimestamp(DirectFunctionCall2(to_timestamp,
                        CStringGetTextDatum(timestring),
                        CStringGetTextDatum("YYYY-MM-DD")));

  sprintf(qanno,"SELECT DISTINCT o.idot FROM sit.otpos o "
                "WHERE btrim(o.codfis) = %s AND to_timestamp(validita) <= %s ORDER BY o.idot;",
          PG_GETARG_CSTRING(0), t);
  elog(INFO, "QUERY %s !",qanno);
//   SPI_connect();
//   res = SPI_exec(qanno,0);

  return 0;
}

score 3 · Accepted Answer · answered Nov 14 '13 at 20:15

you miss a C function signature and magic info

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

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(foo);

Datum foo(PG_FUNCTION_ARGS);

Datum 
foo(PG_FUNCTION_ARGS)
{
    int ret;
    Datum args[1];
    Oid argtypes[1] = { INT4OID };
    Datum result;
    bool isnull;

    SPI_connect();

    args[0] = PG_GETARG_INT32(0);

    /* ensure expected result type by casting */
    ret = SPI_execute_with_args("SELECT ($1 + 10)::int", 
                                   1, argtypes, args, NULL,
                                   true, 1);

    Assert(SPI_processed == 1);

    result = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
    Assert(!isnull);

    SPI_finish();

    PG_RETURN_DATUM(result);
}

Best start is gentle modification PostgreSQL contrib modules. All there is prepared for you - makefiles, some simple templates - https://github.com/postgres/postgres/tree/master/contrib

Pavel in the function you posted here hqo it can be done if ARG is a varchar? I mean arg[0] = PG_GETARG_TEXTP(0); doesn't work for me.. How could it be managed? luca — Luca Marletta, Nov 15 '13 at 14:54
There can be any data type - what error you got? It should be "text *doct = PG_GETARG_TEXT_P(0);" see postgresql/contrib/xml2/xslt_proc.c — Pavel Stehule, Nov 15 '13 at 22:44

PostgreSQL C function suggestion

1 Answers1

Linked