PostgreSQL 7.4.30 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 41. Server Programming Interface | Fast Forward | Next |
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); int execq(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 user 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 execq INFO: 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: 0 INFO: EXECQ: 1 INFO: 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: 0 INSERT 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: 1 INFO: EXECQ: 2 INFO: EXECQ: 1 INFO: EXECQ: 2 INFO: EXECQ: 2 INSERT 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
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.