|
|
This section contains a very simple example of SPI usage. The procedure execq takes an SQL command as its first argument and a row count as its second, executes the command using SPI_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 in src/test/regress/regress.c and in contrib/spi. #include "executor/spi.h"int 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 = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(sql))); SPI_connect(); ret = SPI_exec(command, cnt); proc = SPI_processed; /* * If this is a SELECT and some rows were fetched, * then the rows are printed via elog(INFO). */ 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++) 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: 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 167631 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 167712 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: 0INSERT 167713 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 |
|
|
