This example of SPI usage demonstrates the visibility rule. There are more complex examples in src/test/regress/regress.c and in contrib/spi.
This is a very simple example of SPI usage. The procedure execq accepts an SQL-query in its first argument and tcount in its second, executes the query using SPI_exec and returns the number of tuples for which the query executed:
#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); }
Now, compile and create the function:
CREATE FUNCTION execq (text, integer) RETURNS integer AS '...path_to_so' LANGUAGE C;
vac=> SELECT execq('CREATE TABLE a (x INTEGER)', 0); execq ----- 0 (1 row) vac=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)',0)); INSERT 167631 1 vac=> SELECT execq('SELECT * FROM a',0); NOTICE:EXECQ: 0 <<< inserted by execq NOTICE:EXECQ: 1 <<< value returned by execq and inserted by upper INSERT execq ----- 2 (1 row) vac=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a',1); execq ----- 1 (1 row) vac=> SELECT execq('SELECT * FROM a', 10); NOTICE:EXECQ: 0 NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 <<< 0 + 2, only one tuple inserted - as specified execq ----- 3 <<< 10 is max value only, 3 is real # of tuples (1 row) vac=> DELETE FROM a; DELETE 3 vac=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INSERT 167712 1 vac=> SELECT * FROM a; x - 1 <<< no tuples in a (0) + 1 (1 row) vac=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); NOTICE:EXECQ: 0 INSERT 167713 1 vac=> SELECT * FROM a; x - 1 2 <<< there was single tuple in a + 1 (2 rows) -- This demonstrates data changes visibility rule: vac=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a; NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 NOTICE:EXECQ: 2 INSERT 0 2 vac=> SELECT * FROM a; x - 1 2 2 <<< 2 tuples * 1 (x in first tuple) 6 <<< 3 tuples (2 + 1 just inserted) * 2 (x in second tuple) (4 rows) ^^^^^^^^ tuples visible to execq() in different invocations