Re: [HACKERS] SPI example does not work for 7.1beta4 - Mailing list pgsql-general

Limin Liu <limin@pumpkinnet.com> writes:
> By the way, did you change the whole archetecture of SPI invocation?

No, I told you: what's broken here is the textout() call.  Attached
is the updated example.

> I put
> ---
>    SPI_connect();
>    SPI_exec("create temp table tbl_tmp (n int);",0);
>    SPI_exec("insert into tbl_tmp values (1);",0);
>    SPI_finish();
> ---
> after InitPostgres and before setsigjmp().

I doubt this will work correctly without a transaction around it ...

            regards, tom lane


#include "executor/spi.h"   /* this is what you need to work with SPI */

int execq(text *sql, int cnt);

int
execq(text *sql, int cnt)
{
    char *query;
    int ret;
    int proc;

    /* Convert given TEXT object to a C string */
    query = DatumGetCString(DirectFunctionCall1(textout,
                                                PointerGetDatum(sql)));

    SPI_connect();

    ret = SPI_exec(query, cnt);

    proc = SPI_processed;
    /*
     * If this is SELECT and some tuple(s) fetched -
     * returns tuples to the caller via elog (NOTICE).
     */
    if ( ret == SPI_OK_SELECT && SPI_processed > 0 )
    {
        TupleDesc tupdesc = SPI_tuptable->tupdesc;
        SPITupleTable *tuptable = SPI_tuptable;
        char buf[8192];
        int i,j;

        for (j = 0; j < proc; j++)
        {
            HeapTuple tuple = tuptable->vals[j];

            for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
                sprintf(buf + strlen (buf), " %s%s",
                        SPI_getvalue(tuple, tupdesc, i),
                        (i == tupdesc->natts) ? " " : " |");
            elog (NOTICE, "EXECQ: %s", buf);
        }
    }

    SPI_finish();

    pfree(query);

    return (proc);
}