44.5. Examples

This section contains a very simple example of SPI usage. The procedureexecq takes an SQL command as its first argument and a row count as its second, executes the command usingSPI_exec and returns the number of rows that were processed by the command. You can find more complex examples for SPI in the source tree insrc/test/regress/regress.c and in thespi module.

#include "postgres.h"#include "executor/spi.h"#include "utils/builtins.h"#ifdef PG_MODULE_MAGICPG_MODULE_MAGIC;#endifint execq(text *sql, int cnt);intexecq(text *sql, int cnt){    char *command;    int ret;    int proc;    /* Convert given text object to a C string */    command = text_to_cstring(sql);    SPI_connect();    ret = SPI_exec(command, cnt);    proc = SPI_processed;    /*     * If some rows were fetched, print them via elog(INFO).     */    if (ret > 0 && SPI_tuptable != NULL)    {        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++)                snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",                        SPI_getvalue(tuple, tupdesc, i),                        (i == tupdesc->natts) ? " " : " |");            elog(INFO, "EXECQ: %s", buf);        }    }    SPI_finish();    pfree(command);    return (proc);}

(This function uses call convention version 0, to make the example easier to understand. In real applications you should use the new version 1 interface.)

This is how you declare the function after having compiled it into a shared library (details are inSection 35.9.6.):

CREATE FUNCTION execq(text, integer) RETURNS integer    AS 'filename'    LANGUAGE C;

Here is a sample session:

=> SELECT execq('CREATE TABLE a (x integer)', 0); execq-------     0(1 row)=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));INSERT 0 1=> SELECT execq('SELECT * FROM a', 0);INFO:  EXECQ:  0    -- inserted by execqINFO:  EXECQ:  1    -- returned by execq and inserted by upper INSERT execq-------     2(1 row)=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1); execq-------     1(1 row)=> SELECT execq('SELECT * FROM a', 10);INFO:  EXECQ:  0INFO:  EXECQ:  1INFO:  EXECQ:  2    -- 0 + 2, only one row inserted - as specified execq-------     3              -- 10 is the max value only, 3 is the real number of rows(1 row)=> DELETE FROM a;DELETE 3=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);INSERT 0 1=> SELECT * FROM a; x--- 1                  -- no rows in a (0) + 1(1 row)=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);INFO:  EXECQ:  1INSERT 0 1=> SELECT * FROM a; x--- 1 2                  -- there was one row in a + 1(2 rows)-- This demonstrates the data changes visibility rule:=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;INFO:  EXECQ:  1INFO:  EXECQ:  2INFO:  EXECQ:  1INFO:  EXECQ:  2INFO:  EXECQ:  2INSERT 0 2=> SELECT * FROM a; x--- 1 2 2                  -- 2 rows * 1 (x in first row) 6                  -- 3 rows (2 + 1 just inserted) * 2 (x in second row)(4 rows)               ^^^^^^                       rows visible to execq() in different invocations