PostgreSQL C功能建议[英] PostgreSQL C function suggestion

本文是小编为大家收集整理的关于PostgreSQL C功能建议的处理方法,想解了PostgreSQL C功能建议的问题怎么解决?PostgreSQL C功能建议问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

有人可以给我一个自定义功能的起步暂定的提示吗?

我需要用2个参数,一个varchar和unix时间戳(整数)构造查询 我花了3个小时在下面的几行上,这个结果

可以从pdc_posot_cf_anno('MRDDCU83C12C433F',2012年选择 *查询测试,2012); 我只是想构造正确的SQL查询以传递给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功能签名和魔术信息

#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贡献模块.所有准备都为您准备了 - makefiles,一些简单的模板 - https://github.com/Postgres/Postgres/Tree/Master/contry

本文地址:https://www.itbaoku.cn/post/1763986.html