Cursors

Ordinarily, all rows generated by a SELECT are returned to the client. Cursors allow a SELECT query to be named, and individual result rows retrieved as needed by the client.

Figure  shows an example of cursor usage.  

        
        test=> BEGIN WORK;
        BEGIN
        test=> DECLARE customer_cursor CURSOR FOR
        test-> SELECT customer_id FROM customer;
        SELECT
        test=> FETCH 1 FROM customer_cursor;
         customer_id 
        -------------
                   1
        (1 row) 
         
        test=> FETCH 1 FROM customer_cursor;
         customer_id 
        -------------
                   2
        (1 row) 
         
        test=> FETCH 2 FROM customer_cursor;
         customer_id 
        -------------
                   3
                   4
        (2 rows) 
         
        test=> FETCH -1 FROM customer_cursor;
         customer_id 
        -------------
                   3
        (1 row) 
         
        test=> FETCH -1 FROM customer_cursor;
         customer_id 
        -------------
                   2
        (1 row) 
         
        test=> MOVE 10 FROM customer_cursor;
        MOVE
        test=> FETCH 1 FROM customer_cursor;
         customer_id 
        -------------
                  13
        (1 row) 
        test=> CLOSE customer_cursor;
        CLOSE
        test=> COMMIT WORK;
        COMMIT
         

Note that cursor activity must take place inside a transaction . To declare cursors, you use DECLARE...CURSOR FOR SELECT.... The result rows are retrieved using FETCH. MOVE allows the user to move the cursor position. CLOSE releases all rows stored in the cursor. See the DECLARE, FETCH, MOVE, and CLOSE manual pages for more information.