46.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;#endifPG_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.9.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', 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