PostgreSQL 9.6.21 Documentation | |||
---|---|---|---|
Prev | Up | Chapter 45. Server Programming Interface | 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 the spi module.
#include "postgres.h" #include "executor/spi.h" #include "utils/builtins.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif int64 execq(text *sql, int cnt); int64 execq(text *sql, int cnt) { char *command; int ret; uint64 proc; /* Convert given text object to a C string */ command = text_to_cstring(sql); 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); 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 (details are in Section 36.9.6.):
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 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 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: 1 INSERT 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: 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.