PostgreSQL C函数建议 [英] PostgreSQL C function suggestion

问题描述

有人可以在这个关于自定义函数的开始尝试中给我一个提示吗？

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

我需要用2个参数（一个varchar和一个unix时间戳）构造查询（整数）
我已经花了3个小时用下面的几行显示了这个结果

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

可以选择*从pdc_posot_cf_anno（'MRDDCU83C12C433F' ，2012）;
我只是想构造一个正确的SQL查询以传递给SPI_exec。

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.

非常感谢。

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;
}

推荐答案

您错过了C函数签名和魔术信息

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);
}

最好的开始是对PostgreSQL contrib模块进行温和的修改。一切都为您准备-生成文件，一些简单的模板- https://github.com/ postgres / postgres / tree / master / contrib

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

这篇关于PostgreSQL C函数建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！