c - PostgreSQL C 函数输出一组记录时出现语法错误

Question

我为 PostgreSQL 编写了一个返回一组记录的 C 函数。根据PostgreSQL服务器编程，我输入了这样的代码:

#include "postgres.h"
#include "fmgr.h"
#include "utils/array.h"
#include "executor/spi.h"
#include "utils/builtins.h"
#include "funcapi.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(iknnLP);

int dim;
Heap res;

Datum
iknnLP(PG_FUNCTION_ARGS) {
int call_cntr;
    int call_cntr;
    int max_calls;
    FuncCallContext *funcctx;

    if (SRF_IS_FIRSTCALL()) {
        MemoryContext oldcontext;
        // create a function context for cross-call persistence
        funcctx = SRF_FIRSTCALL_INIT();
        // switch to memory context appropriate for multiple function calls
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        //////////////////////////////////////////////////////////////
        //                     My Code
        //////////////////////////////////////////////////////////////

        funcctx->max_calls = res.length;  // res is a structure
        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);

        // restore memory context
        MemoryContextSwitchTo(oldcontext);
    }

    funcctx = SRF_PERCALL_SETUP();
    call_cntr = funcctx->call_cntr;
    max_calls = funcctx->max_calls;

    if (call_cntr < max_calls) {
        HeapTuple rettuple;
        Datum *retvals;
        bool *retnulls;

        retvals = (Datum*)palloc0(sizeof(Datum) * dim);
        retnulls = (bool*)palloc0(sizeof(bool) * dim);
        for (i = 0; i < dim; i++) {
            retvals[i] = Int32GetDatum(res.rec[call_cntr + 1].vals[i]);
            retnulls[i] = false;
        }
        rettuple = heap_form_tuple(funcctx->tuple_desc, retvals, retnulls);
        SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(rettuple));
    }
    else {
        SRF_RETURN_DONE(funcctx);
    }
}

我的 .sql.in 文件在这里

CREATE OR REPLACE FUNCTION iknnLP(text)
  RETURNS SETOF record
  AS 'MODULE_PATHNAME', 'iknnLP'
  LANGUAGE C STRICT;

不幸的是，我在 PostgreSQL 命令行中调用此函数时遇到语法错误:

select iknnLP('find 3 neighbour of (a0,a1)(31,32) from test') as(a int, b int, distance float);
ERROR:  syntax error at or near "("
LINE 1: ...hbour of (a0,a1)(31,32) from test') as(a int, b ...
                                                 ^

PS:我在制作文件时收到警告:

warning: implicit declaration of function ‘heap_form_tuple’ [-Wimplicit-function-declaration]
rettuple = heap_form_tuple(funcctx->tuple_desc, retvals, retnulls);
^

有人知道原因吗

Answer 1

您的 SQL 语法不完全一致。您正在尝试使用别名构造将字段名称和类型分配给记录。

由于您要返回 SET OF 记录，因此您应该真正使用 FROM 子句中的函数以及记录集的别名:

SELECT a, b, distance
FROM iknnLP('find 3 neighbour of (a0,a1)(31,32) from test') AS x(a int, b int, distance float);