From 66888f7424f7d6c7cea2c26e181054d1455d4e7a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 16 Apr 2007 01:14:58 +0000 Subject: Expose more cursor-related functionality in SPI: specifically, allow access to the planner's cursor-related planning options, and provide new FETCH/MOVE routines that allow access to the full power of those commands. Small refactoring of planner(), pg_plan_query(), and pg_plan_queries() APIs to make it convenient to pass the planning options down from SPI. This is the core-code portion of Pavel Stehule's patch for scrollable cursor support in plpgsql; I'll review and apply the plpgsql changes separately. --- doc/src/sgml/spi.sgml | 322 +++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 318 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index 14e975b5fc9..7ee47162cd7 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -1,4 +1,4 @@ - + Server Programming Interface @@ -799,6 +799,104 @@ SPIPlanPtr SPI_prepare(const char * command, int + + + SPI_prepare_cursor + + + + SPI_prepare_cursor + prepare a plan for a command, without executing it yet + + + SPI_prepare_cursor + + + +SPIPlanPtr SPI_prepare_cursor(const char * command, int nargs, Oid * argtypes, int cursorOptions) + + + + + Description + + + SPI_prepare_cursor is identical to + SPI_prepare, except that it also allows specification + of the planner's cursor options parameter. This is a bitmask + having the values shown in nodes/parsenodes.h + for the options field of DeclareCursorStmt. + SPI_prepare always takes these options as zero. + + + + + Arguments + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + pointer to an array containing the OIDs of + the data types of the parameters + + + + + + int cursorOptions + + + integer bitmask of cursor options; zero produces default behavior + + + + + + + + Return Value + + + SPI_prepare_cursor has the same return conventions as + SPI_prepare. + + + + + Notes + + + Useful bits to set in cursorOptions include + CURSOR_OPT_SCROLL, + CURSOR_OPT_NO_SCROLL, and + CURSOR_OPT_FAST_PLAN. Note in particular that + CURSOR_OPT_HOLD is ignored. + + + + + + SPI_getargcount @@ -1430,7 +1528,9 @@ void SPI_cursor_fetch(Portal portal, bool forw SPI_cursor_fetch fetches some rows from a - cursor. This is equivalent to the SQL command FETCH. + cursor. This is equivalent to a subset of the SQL command + FETCH (see SPI_scroll_cursor_fetch + for more functionality). @@ -1476,6 +1576,15 @@ void SPI_cursor_fetch(Portal portal, bool forw SPI_execute if successful. + + + Notes + + + Fetching backward may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + @@ -1503,8 +1612,9 @@ void SPI_cursor_move(Portal portal, bool forwa SPI_cursor_move skips over some number of rows - in a cursor. This is equivalent to the SQL command - MOVE. + in a cursor. This is equivalent to a subset of the SQL command + MOVE (see SPI_scroll_cursor_move + for more functionality). @@ -1540,6 +1650,210 @@ void SPI_cursor_move(Portal portal, bool forwa + + + Notes + + + Moving backward may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + + + + + + + + SPI_scroll_cursor_fetch + + + + SPI_scroll_cursor_fetch + fetch some rows from a cursor + + + SPI_scroll_cursor_fetch + + + +void SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction, long count) + + + + + Description + + + SPI_scroll_cursor_fetch fetches some rows from a + cursor. This is equivalent to the SQL command FETCH. + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + FetchDirection direction + + + one of FETCH_FORWARD, + FETCH_BACKWARD, + FETCH_ABSOLUTE or + FETCH_RELATIVE + + + + + + long count + + + number of rows to fetch for + FETCH_FORWARD or + FETCH_BACKWARD; absolute row number to fetch for + FETCH_ABSOLUTE; or relative row number to fetch for + FETCH_RELATIVE + + + + + + + + Return Value + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + Notes + + + See the SQL command + for details of the interpretation of the + direction and + count parameters. + + + + Direction values other than FETCH_FORWARD + may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + + + + + + + + SPI_scroll_cursor_move + + + + SPI_scroll_cursor_move + move a cursor + + + SPI_scroll_cursor_move + + + +void SPI_scroll_cursor_move(Portal portal, FetchDirection direction, long count) + + + + + Description + + + SPI_scroll_cursor_move skips over some number of rows + in a cursor. This is equivalent to the SQL command + MOVE. + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + FetchDirection direction + + + one of FETCH_FORWARD, + FETCH_BACKWARD, + FETCH_ABSOLUTE or + FETCH_RELATIVE + + + + + + long count + + + number of rows to move for + FETCH_FORWARD or + FETCH_BACKWARD; absolute row number to move to for + FETCH_ABSOLUTE; or relative row number to move to for + FETCH_RELATIVE + + + + + + + + Return Value + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + Notes + + + See the SQL command + for details of the interpretation of the + direction and + count parameters. + + + + Direction values other than FETCH_FORWARD + may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + -- cgit v1.2.3