46.6. Examples
This section contains a very simple example of SPI usage. The C functionexecq 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"PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(execq);Datumexecq(PG_FUNCTION_ARGS){ char *command; int cnt; int ret; uint64 proc; /* Convert given text object to a C string */ command = text_to_cstring(PG_GETARG_TEXT_PP(0)); cnt = PG_GETARG_INT32(1); 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]; uint64 j; for (j = 0; j < proc; j++) { HeapTuple tuple = tuptable->vals[j]; int i; 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); PG_RETURN_INT64(proc);}This is how you declare the function after having compiled it into a shared library (details are inSection 37.10.5.):
CREATE FUNCTION execq(text, integer) RETURNS int8 AS 'filename' LANGUAGE C STRICT;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 RETURNING *', 1);INFO: EXECQ: 2-- 0 + 2, then execution was stopped by count execq------- 1(1 row)=> SELECT execq('SELECT * FROM a', 10);INFO: EXECQ: 0INFO: EXECQ: 1INFO: EXECQ: 2 execq------- 3-- 10 is the max value only, 3 is the real number of rows(1 row)=> SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1); execq------- 3-- all rows processed; count does not stop it, because nothing is returned(1 row)=> SELECT * FROM a; x---- 0 1 2 10 11 12(6 rows)=> DELETE FROM a;DELETE 6=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);INSERT 0 1=> SELECT * FROM a; x--- 1-- 0 (no rows in a) + 1(1 row)=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);INFO: EXECQ: 1INSERT 0 1=> SELECT * FROM a; x--- 1 2-- 1 (there was one row in a) + 1(2 rows)-- This demonstrates the data changes visibility rule.-- execq is called twice and sees different numbers of rows each time:=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;INFO: EXECQ: 1-- results from first execqINFO: EXECQ: 2INFO: EXECQ: 1-- results from second execqINFO: 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)