Cursors for backward scrolling are very useful if you want to process the subset of data starting at the end and moving backward, row by row until you reach the first row in your result set.
CrateDB already supports cursors, and since v5.2, you can also enjoy the support for backward scrolling with standard SQL commands.
Cursors are an important feature because they allow for more precise control over data manipulation. They can be useful for tasks such as looping through a result set and updating or deleting specific rows. They allow you to perform some operations on a large dataset without loading the entire dataset into client-side memory at once. Instead, with cursors, you can process data in smaller chunks, which is more efficient in terms of memory usage.
CrateDB now supports the option for backward scrolling through the result set with the cursor. This allows the user to easily access and manipulate data in reverse order without having to iterate through the result set manually.
An interesting use case for scrolling with cursors is pagination. Pagination with cursor can be used to retrieve a limited number of records from a large dataset, with the ability to move forward or backward through the dataset. For example, a website may have a database of products with thousands of items and want to display them on multiple pages. Instead of retrieving all items and displaying them at once, which could take a long time and use a lot of memory, the website can use pagination with cursors to retrieve a smaller subset of items simultaneously.
With cursors, you can also retrieve a specific row within the result set. For instance, after going through the product list, you might want to jump back to the specific row and continue scrolling forward, repeating the result set, and this can be done multiple times, thus looping multiple times through the result set. In CrateDB, you can move to the exact row in the result set by using the ABSOLUTE position command, as illustrated later in this article.
To show how cursors can be used in CrateDB, let’s first use a table that stores information about products we want to show on the website:
CREATE TABLE product (product_id INT, name TEXT, price INT, amount INT);
Before creating the cursor, we need to issue BEGIN command to create a new transaction block. With BEGIN command you start a scope in which the cursor can be declared:
BEGIN;
DECLARE statement creates a new cursor. To be able to use the cursor for backward scrolling we need to declare it with the SCROLL option:
DECLARE product_cursor SCROLL CURSOR FOR
SELECT *
FROM product
ORDER BY product_id;
In our example, the product_cursor holds the data from the product table ordered by product_id. To retrieve data from the cursor use the FETCH statement. For instance, let’s fetch the first 10 products from the result set:
FETCH FORWARD 10 FROM product_cursor;
Now, let’s move to the 20th product in the list by using FETCH ABSOLUTE:
FETCH ABSOLUTE 20 FROM product_cursor;
If at some point, we want to go back through the result set, we can use the FETCH BACKWARD statement. The following command shows you how to move to the row preceding the current row:
FETCH BACKWARD FROM product_cursor;
If you want to fetch the row that is 10 rows back from the current one, use the BACKWARD count statement as illustrated below:
FETCH BACKWARD 10 FROM product_cursor;
By using FORWARD and BACKWARD options, we can scroll the list in both directions. Finally, if you no longer need the cursor, you can close it with the CLOSE command:
CLOSE product_cursor;
Cursors are an important tool for efficiently accessing and manipulating data in SQL databases. If you want to learn more about cursors and CrateDB in general, check out our documentation and join our community.