Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using Cursors

A SQL cursor in PostgreSQL is a read-only pointer to a fully executed SELECT statement's result set. Cursors are typically used within applications that maintain a persistent connection to the PostgreSQL backend. By executing a cursor, and maintaining a reference to its returned result set, an application can more efficiently manage which rows to retrieve from a result set at different times, without having to re-execute the query with different LIMIT and OFFSET clauses.

Used within a programming Application Programming Interface (API), cursors are often used to allow multiple queries to be executed to a single database backend, which are then tracked and managed separately by the application through references to the cursor. This prevents having to store all of the results in memory within the application.

Cursors are often abstracted within a programming API (such as libpq++ 's PgCursor class), though they can also be directly created and manipulated through standard SQL commands. For the sake of generality, this section uses psql to demonstrate the fundamental concepts of cursors with SQL. The four SQL commands involved with PostgreSQL cursors are DECLARE, FETCH, MOVE and CLOSE.

The DECLARE command both defines and opens a cursor, in effect defining the cursor in memory, and then populating the cursor with information about the result set returned from the executed query. The FETCH command lets you pull rows from an open cursor. The MOVE command moves the "current" location of the cursor within the result set, and the CLOSE command closes the cursor, freeing up any associated memory.

Note: If you are interested in learning how to use cursors within a particular API, consult that API's documentation.

Declaring a cursor

A cursor is both created and executed with the DECLARE SQL command. This process is also referred to as "opening" a cursor. A cursor may be declared only within an existing transaction block, so you must execute a BEGIN command prior to declaring a cursor. Here is the syntax for DECLARE:

  DECLARE 
cursorname
 [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
                     CURSOR FOR 
query

                     [ FOR { READ ONLY | UPDATE [ OF 
column
 [, ...] ] } ]

DECLARE cursorname

cursorname is the name of the cursor to create.

[ BINARY ]

The optional BINARY keyword causes output to be retrieved in binary format instead of standard ASCII; this can be more efficient, though it is only relevant to custom applications, as clients such as psql are not built to handle anything but text output.

[ INSENSITIVE ] [ SCROLL ]

The INSENSITIVE and SCROLL keywords exist for compliance with the SQL standard, though they each define PostgreSQL's default behavior and are never necessary. The INSENSITIVE SQL keyword exists to ensure that all data retrieved from the cursor remains unchanged from other cursors or connections. Since PostgreSQL requires that cursors be defined within transaction blocks, this behavior is already implied. The SCROLL SQL keyword exists to specify that multiple rows can be selected at a time from the cursor. This is the default in PostgreSQL, even if unspecified.

CURSOR FOR query

query is the complete query whose result set will be accessible by the cursor, when executed.

[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

As of PostgreSQL 7.1.x, cursors may only be defined as READ ONLY, and the FOR clause is therefore superfluous.

Example 7-42 begins a transaction block with the BEGIN keyword, and opens a cursor named all_books with SELECT * FROM books as its executed SQL statement.

Example 7-42. Declaring a cursor

booktown=# 
BEGIN;

BEGIN
booktown=# 
DECLARE all_books CURSOR

booktown-# 
        FOR SELECT * FROM books;

SELECT

The SELECT message returned from Example 7-42 indicates that the statement was executed successfully. This means that the rows retrieved by the query are now accessible from the all_books cursor.

Fetching from a cursor

You may retrieve rows from a cursor with the FETCH SQL command. Here is the syntax for the FETCH SQL command:

  FETCH [ FORWARD | BACKWARD | RELATIVE ]
        [ # | ALL | NEXT | PRIOR ]
        { IN | FROM } 
cursor

In this syntax diagram, cursor is the name of the cursor from which to retrieve row data. A cursor always points to a "current" position in the executed statement's result set, and rows can be retrieved either ahead or behind of the current location. The FORWARD and BACKWARD keywords may be used to specify the direction, though the default is forward. The RELATIVE keyword is a noise term made available for SQL92 compliance.

Warning

The ABSOLUTE keyword can be used, but absolute cursor positioning and fetching are not supported as of PostgreSQL 7.1.x; the cursor will still use relative positioning and provide a notice regarding the state of absolute positioning being unsupported.

Following the direction you may optionally specify a quantity. This quantity may either be a literal number of rows to be returned (in the form of an integer constant) or one of several keywords. The ALL keyword causes returns all rows from the current cursor position. The NEXT keyword (the default) returns the next single row from the current cursor position. The PRIOR keyword causes the single row preceding the current cursor position to be returned.

There is no functional difference between the IN and FROM keywords, but one of these must be specified.

Example 7-43 fetches the first four rows stored in the result set pointed to by the all_books cursor. As a direction is not specified, FORWARD is implied. It then uses a FETCH statement with the NEXT keyword to select the fifth row, and then another FETCH statement with the PRIOR keyword to again select the fourth retrieved row.

Example 7-43. Fetching rows from a cursor

booktown=# 
FETCH 4 FROM all_books;

  id  |         title         | author_id | subject_id
------+-----------------------+-----------+------------
 7808 | The Shining           |      4156 |          9
 4513 | Dune                  |      1866 |         15
 4267 | 2001: A Space Odyssey |      2001 |         15
 1608 | The Cat in the Hat    |      1809 |          2
(4 rows)

booktown=# 
FETCH NEXT FROM all_books;

  id  |            title            | author_id | subject_id
------+-----------------------------+-----------+------------
 1590 | Bartholomew and the Oobleck |      1809 |          2
(1 row)

booktown=# 
FETCH PRIOR FROM all_books;

  id  |       title        | author_id | subject_id
------+--------------------+-----------+------------
 1608 | The Cat in the Hat |      1809 |          2
(1 row)

Moving a cursor

A cursor maintains a position in the result set of its referenced SELECT statement. You can use the MOVE command to move the cursor to a specified row position in that result set. Here is the syntax for the MOVE command:

  MOVE [ FORWARD | BACKWARD | RELATIVE ]
       [ # | ALL | NEXT | PRIOR ]
       { IN | FROM } 
cursor

As you can see, the syntax is very similar to FETCH. However, the MOVE command does not retrieve any rows and only moves the current position of the specified cursor . The amount is specified by either an integer constant, the ALL keyword (to move as far as can be moved in the specified direction), NEXT, or PRIOR. Example 7-44 moves the cursor forward 10 rows from its current position in the result set.

Example 7-44. Moving a cursor

booktown=# 
MOVE FORWARD 10

booktown-# 
     IN all_books;

MOVE

Closing a cursor

Use the CLOSE command to explicitly close an open cursor. A cursor is also implicitly closed if the transaction block that it resides within is committed with the COMMIT command, or rolled back with the ROLLBACK command.

Here is the syntax for CLOSE, where cursorname is the name of the cursor intended to be closed:

  CLOSE 
cursorname

Example 7-45 closes the all_books cursor, freeing the associated memory, and rendering the cursor's results inaccessible.

Example 7-45. Closing a cursor

booktown=# 
CLOSE all_books;

CLOSE
booktown=# 
COMMIT;

COMMIT
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire