Executing a query and returning the result set using the SPI - Mailing list pgsql-general

Hello all,

I'm messing around with the Server Programming Interface and the
particular example presented at:

http://www.postgresql.org/docs/current/interactive/spi-examples.html

Ideally, I would want to make the example function return the
information as a "set" and not through elog() so I can later access it
and print it using PHP.

I have a few ideas on how this can be accomplished but I haven't found
any simple example to fully elucidate me. I have been reading:

http://www.postgresql.org/docs/current/interactive/xfunc-c.html (33.7.9)

The code right now compiles fine but it crashes the server on loading:

veiculos=# CREATE FUNCTION teste(text) RETURNS setof veiculo AS
'teste.so' LANGUAGE C;
veiculos=# select * from teste('select * from veiculo');
server closed the connection unexpectedly

This is PostgreSQL version: 7.4.1

I'm compiling the code with:

$ gcc -Wall -fpic -c -I` pg_config --includedir` teste.c
$ gcc -shared -o teste.so teste.o

Here's the code:

#include "server/postgres.h"
#include "server/fmgr.h"
#include "server/executor/spi.h"
#include "server/funcapi.h"
#include "server/access/heapam.h"
#include "server/catalog/pg_type.h"
#include "server/storage/lock.h"
#include "server/storage/proc.h"
#include "server/utils/builtins.h"

PG_FUNCTION_INFO_V1(teste);

Datum teste(PG_FUNCTION_ARGS) {
    char *command;
    int ret;
    FuncCallContext     *funcctx;
    TupleDesc            tupdesc;
    TupleTableSlot      *slot;
    AttInMetadata       *attinmeta;
    int                  call_cntr;
    int                  max_calls;
    text  *sql_command = PG_GETARG_TEXT_P(0);

    command = DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(sql_command)));
    SPI_connect();

    ret = SPI_exec(command, 0);

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

    /* total number of tuples to be returned */
    funcctx->max_calls = PG_GETARG_UINT32(0);

    /*
     * Build a tuple description for a tuple
     */
    tupdesc = SPI_tuptable->tupdesc;

    /* allocate a slot for a tuple with this tupdesc */
    slot = TupleDescGetSlot(tupdesc);

    /* assign slot to function context */
    funcctx->slot = slot;

    /*
     * Generate attribute metadata needed later to produce tuples from raw
     * C strings
     */
    attinmeta = TupleDescGetAttInMetadata(tupdesc);
    funcctx->attinmeta = attinmeta;

    MemoryContextSwitchTo(oldcontext);
    }

    /* stuff done on every call of the function */
    funcctx = SRF_PERCALL_SETUP();

    call_cntr = funcctx->call_cntr;
    max_calls = funcctx->max_calls;
    slot = funcctx->slot;
    attinmeta = funcctx->attinmeta;

    if (call_cntr < max_calls) {    /* do when there is more left to
send */         char       **values;
    HeapTuple    tuple;
    Datum        result;

    /*
     * Prepare a values array for storage in our slot.
     * This should be an array of C strings which will
     * be processed later by the appropriate "in" functions.
     */
    values = (char **) palloc(3 * sizeof(char *));
    values[0] = (char *) palloc(16 * sizeof(char));
    values[1] = (char *) palloc(16 * sizeof(char));
    values[2] = (char *) palloc(16 * sizeof(char));

    snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
    snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
    snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));

    /* build a tuple */
    tuple = BuildTupleFromCStrings(attinmeta, values);

    /* make the tuple into a datum */
    result = TupleGetDatum(slot, tuple);

    /* Clean up (this is not actually necessary) */
    pfree(values[0]);
    pfree(values[1]);
    pfree(values[2]);
    pfree(values);

    SRF_RETURN_NEXT(funcctx, result);
    }
    else {    /* do when there is no more left */
    SRF_RETURN_DONE(funcctx);
    }

    SPI_finish();
    pfree(command);

}

--
Nuno Morgadinho
Undergraduate Computer Science Student
Évora University, Portugal