From d5466e38f0fbc196b250d082bdab62728de036f9 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 1 Apr 2008 03:09:30 +0000 Subject: Add SPI-level support for executing SQL commands with one-time-use plans, that is commands that have out-of-line parameters but the plan is prepared assuming that the parameter values are constants. This is needed for the plpgsql EXECUTE USING patch, but will probably have use elsewhere. This commit includes the SPI functions and documentation, but no callers nor regression tests. The upcoming EXECUTE USING patch will provide regression-test coverage. I thought committing this separately made sense since it's logically a distinct feature. --- doc/src/sgml/spi.sgml | 312 ++++++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 301 insertions(+), 11 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index a28d8170a4c..04cd0ca1791 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -1,4 +1,4 @@ - + Server Programming Interface @@ -267,7 +267,7 @@ void SPI_pop(void) Description - SPI_pop pops the previous environment from the + SPI_pop pops the previous environment from the SPI call stack. See SPI_push. @@ -371,7 +371,7 @@ SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5); then you can use the global pointer SPITupleTable *SPI_tuptable to access the result rows. Some utility commands (such as - EXPLAIN) also return row sets, and SPI_tuptable + EXPLAIN) also return row sets, and SPI_tuptable will contain the result in these cases too. @@ -676,6 +676,150 @@ int SPI_exec(const char * command, long count< + + + SPI_execute_with_args + + + + SPI_execute_with_args + execute a command with out-of-line parameters + + + SPI_execute_with_args + + + +int SPI_execute_with_args(const char *command, + int nargs, Oid *argtypes, + Datum *values, const char *nulls, + bool read_only, long count) + + + + + Description + + + SPI_execute_with_args executes a command that might + include references to externally supplied parameters. The command text + refers to a parameter as $n, and + the call specifies data types and values for each such symbol. + read_only and count have + the same interpretation as in SPI_execute. + + + + The main advantage of this routine compared to + SPI_execute is that data values can be inserted + into the command without tedious quoting/escaping, and thus with much + less risk of SQL-injection attacks. + + + + Similar results can be achieved with SPI_prepare followed by + SPI_execute_plan; however, when using this function + the query plan is customized to the specific parameter values provided. + For one-time query execution, this function should be preferred. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. + + + + + Arguments + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + an array containing the OIDs of + the data types of the parameters + + + + + + Datum * values + + + an array of actual parameter values + + + + + + const char * nulls + + + an array describing which parameters are null + + + + If nulls is NULL then + SPI_execute_with_args assumes that no parameters are + null. + + + + + + bool read_only + + + true for read-only execution + + + + + + long count + + + maximum number of rows to process or return + + + + + + + + Return Value + + + The return value is the same as for SPI_execute. + + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + + SPI_prepare @@ -861,7 +1005,7 @@ SPIPlanPtr SPI_prepare_cursor(const char * command, int < - + int cursorOptions @@ -1453,6 +1597,152 @@ Portal SPI_cursor_open(const char * name, SPIPlanPtr + + + SPI_cursor_open_with_args + + + + SPI_cursor_open_with_args + set up a cursor using a query and parameters + + + SPI_cursor_open_with_args + + + +Portal SPI_cursor_open_with_args(const char *name, + const char *command, + int nargs, Oid *argtypes, + Datum *values, const char *nulls, + bool read_only, int cursorOptions) + + + + + Description + + + SPI_cursor_open_with_args sets up a cursor + (internally, a portal) that will execute the specified query. + Most of the parameters have the same meanings as the corresponding + parameters to SPI_prepare_cursor + and SPI_cursor_open. + + + + For one-time query execution, this function should be preferred + over SPI_prepare_cursor followed by + SPI_cursor_open. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. + + + + The passed-in data will be copied into the cursor's portal, so it + can be freed while the cursor still exists. + + + + + Arguments + + + + const char * name + + + name for portal, or NULL to let the system + select a name + + + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + an array containing the OIDs of + the data types of the parameters + + + + + + Datum * values + + + an array of actual parameter values + + + + + + const char * nulls + + + an array describing which parameters are null + + + + If nulls is NULL then + SPI_cursor_open_with_args assumes that no + parameters are null. + + + + + + bool read_only + + + true for read-only execution + + + + + + int cursorOptions + + + integer bitmask of cursor options; zero produces default behavior + + + + + + + + Return Value + + + Pointer to portal containing the cursor. Note there is no error + return convention; any error will be reported via elog. + + + + + + SPI_cursor_find @@ -1748,7 +2038,7 @@ void SPI_scroll_cursor_fetch(Portal portal, FetchDirectio See the SQL command - for details of the interpretation of the + for details of the interpretation of the direction and count parameters. @@ -1847,7 +2137,7 @@ void SPI_scroll_cursor_move(Portal portal, FetchDirection See the SQL command - for details of the interpretation of the + for details of the interpretation of the direction and count parameters. @@ -3346,9 +3636,9 @@ execq(text *sql, int cnt) 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). @@ -3359,11 +3649,11 @@ execq(text *sql, int cnt) 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), @@ -3469,7 +3759,7 @@ INSERT 0 2 2 2 -- 2 rows * 1 (x in first row) 6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row) -(4 rows) ^^^^^^ +(4 rows) ^^^^^^ rows visible to execq() in different invocations -- cgit v1.2.3