PostgreSQL C扩展函数:表作为参数和结果

kpbwa7wx  于 2023-04-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(116)

我需要用C为PostgreSQL写一个扩展函数:
1.有一个double类型的3列表作为参数
1.结果是一个double类型的3列表
注意,理想情况下,模式应该是物化模式,因为函数将通过转换整个输入表上的数据来生成新表。
我确实找到了与将结果作为集合(多行)相关的信息。但是函数的测试并没有得到列的值--我只是得到了一个带有(val,val,val)的记录。
有没有人能告诉我一些信息,或者最好是一个简单的例子函数,有这个基本的类型?
谢谢大家!
我试图在PostgreSQL文档中找到信息,但并不清楚,特别是当谈到表作为输入参数和在物化模式下返回时。

muk1a3rh

muk1a3rh1#

根据LaurenzAlbe的建议,我创建了如下内容。
请注意,我并没有确切地找到如何接收一个表作为函数的输入。这里的想法是将其 Package 到SQL函数中,以便它将创建一个临时表,填充这个临时表,然后将这个临时表的名称传递给C函数。
对于我的应用来说,这就足够了。但如果其他人愿意帮忙的话,我很乐意讨论这一点并加以改进。
谢啦

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include "funcapi.h"
#include "executor/executor.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/tuplestore.h"
#include "catalog/pg_type.h"
#include "executor/spi.h"
#include "access/heapam.h"
#include "access/htup_details.h"
#include "miscadmin.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PGDLLEXPORT Datum add_table(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(add_table);

Datum add_table(PG_FUNCTION_ARGS) {

    ReturnSetInfo* rsinfo = (ReturnSetInfo*)fcinfo->resultinfo;

    /* check to see if caller supports us returning a tuplestore */
    if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
        ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("set-valued function called in context that cannot accept a set")));
    if (!(rsinfo->allowedModes & SFRM_Materialize))
        ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("materialize mode required, but it is not allowed in this context")));

    /* The name of the table must be given as string 'tablename'::regclass */
    char* inputTableOid = PG_GETARG_OID(0);

    /* Executes the select statement to get input table */
    StringInfoData commandBuffer;
    initStringInfo(&commandBuffer);
    appendStringInfo(&commandBuffer, "SELECT * FROM %s", get_rel_name(inputTableOid));

    /* Starts SPI */
    int connRes = SPI_connect();
    if (connRes != SPI_OK_CONNECT) {
        elog(ERROR, "SPI_connect failed: %d", connRes);
    }
    int selectRes = SPI_exec(commandBuffer.data, 0);
    if (selectRes != SPI_OK_SELECT) {
        elog(ERROR, "SELECT failed: %d", selectRes);
    }

    /* Saves the select results */
    SPITupleTable* inputTupTable = SPI_tuptable; // The pointer to the input table - copies the global var that is filled by SPI_exec
    int inputNRows = SPI_processed; // The actual number of returned rows - copies the global var that is filled by SPI_exec
    TupleDesc inputTupleDesc = inputTupTable->tupdesc; // The TupleDesc of the input table
    int natts = inputTupleDesc->natts; // The number of attributes in the input tuple (cols)

    /* The tupdesc and tuplestore must be created in ecxt_per_query_memory */
    MemoryContext oldcontext = MemoryContextSwitchTo(rsinfo->econtext->ecxt_per_query_memory);

    /* Makes the output TupleDesc */
    TupleDesc tupdesc = CreateTemplateTupleDesc(3);
    TupleDescInitEntry(tupdesc, (AttrNumber)1, "x", FLOAT4OID, -1, 0);
    TupleDescInitEntry(tupdesc, (AttrNumber)2, "y", FLOAT4OID, -1, 0);
    TupleDescInitEntry(tupdesc, (AttrNumber)3, "z", FLOAT4OID, -1, 0);
    tupdesc = BlessTupleDesc(tupdesc);

    /* Checks if random access is allowed */
    bool randomAccess = (rsinfo->allowedModes & SFRM_Materialize_Random) != 0;
    /* Starts the tuplestore */
    Tuplestorestate* tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);

    /* Set the output */
    rsinfo->returnMode = SFRM_Materialize;
    rsinfo->setResult = tupstore;
    rsinfo->setDesc = tupdesc;

    /* Returns to the old context */
    MemoryContextSwitchTo(oldcontext);

    /* Fills the output table */
    for (size_t i = 0; i < inputNRows; i++) {
        /* Getting the values of the current row */
        HeapTuple inputTuple = inputTupTable->vals[i]; // Gets the current input tuple (row)

        Datum* inputValues = palloc(natts * sizeof(Datum));
        bool* inputNulls = palloc(natts * sizeof(bool));
        heap_deform_tuple(inputTuple, inputTupleDesc, inputValues, inputNulls); // Gets the value of the current row

        /* Adds 1 to each column of the input row */
        Datum* outputValues = palloc(natts * sizeof(Datum));
        for (size_t j = 0; j < natts; j++) {
            outputValues[j] = Float4GetDatum(DatumGetFloat4(inputValues[j]) + 1);
        }

        /* Builds the output tuple (row) */
        HeapTuple outputTuple = heap_form_tuple(tupdesc, outputValues, inputNulls);
        /* Puts in the output tuplestore */
        tuplestore_puttuple(tupstore, outputTuple);
    }

    return (Datum)0;

}

然后,要将link此扩展添加到postgresql中,请使用以下代码:

-- FUNCTION: public.add_table(oid)

DROP FUNCTION IF EXISTS public.add_table(oid);

CREATE OR REPLACE FUNCTION public.add_table(
    oid,
    OUT x real,
    OUT y real,
    OUT z real)
    RETURNS SETOF record 
    LANGUAGE 'c'
    COST 1
    IMMUTABLE STRICT PARALLEL UNSAFE
    ROWS 1000

AS '...Path.../<dllName>.dll', 'add_table'
;

ALTER FUNCTION public.add_table(oid)
    OWNER TO postgres;

要调用函数:

-- The table contains 3 real columns
select * from add_table('test_table'::regclass);

这将返回所有值都递增的表。

相关问题