Cursors

This document describes how to use YSQL cursors to process a result set query one row at a time.

Overview

A cursor represents a pointer with read-only access to the result set of a SELECT query. Cursors allow you to encapsulate a query and process individual rows. This enables you to divide a large result set into parts for processing of each part, thus making your query more efficient and preventing memory overflow.

You can create a function that returns a reference to a cursor, therefore returning a large result set ready to be processed by the caller of the function. You can also use cursors in procedures.

The following statements, in order, are associated with YSQL cursors:

• DECLARE – Allows you to create and execute the cursor inside a transaction.

• OPEN – Allows you to open the cursor.

• FETCH – Allows you to retrieve rows from an open cursor.

• CLOSE – Allows you to close the cursor and release memory used during the process.

Operations involving cursors must be performed inside transactions.

Declaring a Cursor

You need to declare a cursor before you can open and use it. There are two ways to declare a cursor:

• As a variable of type refcursor placed in the YSQL block's declaration section, as demonstrated by the following syntax:

  DECLARE new_cursor refcursor;
  

• As an element bound to a query, based on the following syntax:

  new_cursor CURSOR [( arguments )] FOR a_query;
  

  new_cursor represents a variable name for the cursor. arguments represents a comma-separated list of query parameters that are substituted by values when the cursor is opened. a_query represents any SELECT statement.

The following example shows how to declare cursors for the employees table:

DECLARE
  employees_cursor_1 refcursor;
  employees_cursor_2 CURSOR FOR SELECT * FROM employees;

employees_cursor_1 is not bound to a query so it can be used with any query, whereas employees_cursor_2 encapsulates all rows in the employees table and is bound to s specific query.

Opening a Cursor

The following example shows how to open an unbound cursor. Since the cursor variable was not bounded to any query when the cursor was declared, you need to specify the query when you are opening the cursor.

OPEN employees_cursor_1 FOR SELECT * FROM employees;

The following example shows how to open a cursor that was already bound to a query at the time of its declaration. In this case, if there are any query parameters, you have to pass them.

OPEN employees_cursor_2;

Using a Cursor

You can use an open cursor with the YSQL data manipulation statements such as FETCH and DELETE.

How to Fetch Rows

Using the FETCH statement, you can obtain all rows or a specific row from the cursor and place it into a target such as a record, a row variable, or a comma-separated list of variables. When the rows are exhausted, the target is set toNULL.

The following examples show how to apply the FETCH statement to a cursor:

FETCH ALL FROM employees_cursor_2;

FETCH FROM employees_cursor_2 INTO employees_row;

How to Delete Rows

The following example shows how to delete all rows from a cursor:

DELETE FROM employees_cursor_2;

How to Return a Cursor

You can return a cursor using a function that opens the cursor and returns its name to the caller which, in turn, can fetch rows from the cursor and close it before the end of the transaction, if needed.

For more information and examples, refer to the "Returning Cursors" section in Using Cursors.

How to Use Loops

You can iterate through the result set of a bound cursor using a certain form of the FOR statement, as per the following syntax:

FOR rec_var
IN bound_cursor_var [ ( [ argument_name := ] argument_value [, ...] ) ]
LOOP
  statements
END LOOP;

The cursor is automatically opened by the FOR statement and closed when the loop exits. If the cursor was declared to accept arguments, a list of argument value expressions must appear (they will be substituted in the query).

rec_var is always of type record. This variable's lifecycle is limited by the loop, with each row returned by the cursor assigned to it as the loop body is executed.

Closing a Cursor

You use the CLOSE statement to complete the cursor lifecycle. By closing the cursor, you release resources before the end of the transaction. This also releases the cursor variable which allows you to open the cursor again.

The following example shows how to close a cursor:

CLOSE employees_cursor_2;

Examples

Suppose you work with a database that includes the following table populated with data:

CREATE TABLE employees (
  employee_no integer,
  name text,
  department text
);

INSERT INTO employees VALUES
(1221, 'John Smith', 'Marketing'),
(1222, 'Bette Davis', 'Sales'),
(1223, 'Lucille Ball', 'Operations');

The following example shows how to declare a cursor and use it to retrieve all rows from the employees table:

BEGIN;
  DECLARE employees_cursor_2 CURSOR FOR SELECT * FROM employees;
  FETCH ALL FROM employees_cursor_2;
END;

The following is the output produced by the preceding example:

 employee_no | name         | department
-------------+--------------+---------------
 1221        | John Smith   | Marketing
 1222        | Bette Davis  | Sales
 1223        | Lucille Ball | Operations