Data Navigation with PostgreSQL Cursors - Part I

Navigating large datasets quickly and effectively is critical when it comes to database management systems. One of PostgreSQL's core features, cursors are vital tools for accurately navigating and modifying query results. During the migration from Oracle to PostgreSQL, managing data efficiently using PostgreSQL Cursors for precise navigation is crucial. This article is the first in a series of three, where we will explore the three different types of cursors you can use with PostgreSQL. Each article will focus on one cursor type, making it easy to understand and apply them in PostgreSQL. In this article, we will discuss the type of Cursors in PostgreSQL and how we can navigate through data with precision using Scrollable Cursors in PostgreSQL.

Data Navigation with PostgreSQL Cursors.

• Scrollable Cursors
• Non-Scrollable Cursors
• With-Hold Cursors

If interested, take a look at our article on PostgreSQL Summary and Achievements in 2023.

Understanding Cursors in PostgreSQL

A cursor is a database object used to retrieve rows from a result set one at a time, rather than fetching the entire result set at once. They provide a way to iterate through the rows of a result set, allowing controlled and sequential access to the data. It acts as a pointer to a specific row within a set of rows and allows operations like traversal, reading, updating, and deleting individual rows.

Types of PostgreSQL Cursors

In PostgreSQL, various cursor types offer flexibility in managing query results. Forward-only cursors, enable sequential navigation through result sets but restrict movement to forward direction only. Scrollable cursors provide bidirectional movement, allowing data retrieval in both forward and backward directions, enhancing navigation within result sets. Additionally, PostgreSQL supports cursor holdability, enabling cursors to remain open even after committing a transaction, ensuring continued access to results.

1. Scrollable Cursors
2. Non-Scrollable Curosrs
3. With-Hold Cursors

If you are looking for any assistance around PostgreSQL, click here for personalized expertise to elevate your database performance and meet your unique business needs.

Scrollable Cursors

Scrollable Cursors enable movement both forward and backward within query results, offering a more dynamic approach to data retrieval and manipulation compared to traditional forward-only cursors. These cursors empower developers to fetch rows in a non-sequential manner, providing greater flexibility in accessing and processing data sets.

Consider a table named employees. Let's prepare the cursor to navigate through all records within the "employees" table

DECLARE my_cursor SCROLL CURSOR FOR SELECT * FROM employees;

Cursor can be opened using the following command, it will make the cursor available to fetch the data from it.

OPEN my_cursor;

Fetching the data from cursor

PostgreSQL supports multiple FETCH methods to retrieve the data from cursor - NEXT - PRIOR - FIRST - LAST - RELATIVE [number] - ABSOLUTE [number]

FETCH NEXT NEXT will retrieve the next row in the result set and advances the cursor position forward. If the row value exists, it will print it; otherwise, NULL will be produced.

FETCH NEXT FROM my_cursor;

Move backwards using PRIOR PRIOR retrieves the previous row in the result set and moves the cursor position backward.

FETCH PRIOR FROM my_cursor;

To get the FIRST row FIRST takes the cursor to the first position and displays the first row in the result set.

FETCH FIRST FROM my_cursor;

Using RELATIVE It moves the cursor to a relative position from its current location in the result set and retrieves the row.

FETCH RELATIVE 2 FROM my_cursor;

Negative numbering indicates to iterate backwards. It will retrieve the previous row of the result set which is same as the behavior of FETCH PRIOR

FETCH RELATIVE -1 FROM my_cursor;

Moving the cursor

MOVE moves the pointer but doesn't get any info. MOVE functions in the same way as FETCH, with the exception that it just moves the cursor and doesn't return the row it went to. By default, MOVE moves the pointer to the next row.

MOVE my_cursor;

RELATIVE, ABSOLUTE, FIRST, LAST can also be used with MOVE.

Let's write an anonymous block to fetch one record from employees table and print it to the console using FETCH command.

DO $$
DECLARE
    emp_record employees%ROWTYPE;
    emp_cursor SCROLL CURSOR FOR SELECT * FROM employees LIMIT 1;
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO emp_record;
    CLOSE emp_cursor;

    RAISE NOTICE 'Employee: %', emp_record; -- Prints employee data to the console
END $$;

Conclusion

We covered Scrollable cursors in this blog post. PostgreSQL provide a flexible and efficient way to manage query outputs, especially when dealing with extensive datasets. In the further articles of the series in cursors, we will look deeply into Non-Scrollable Cursors, With-Hold Cursors and advantages and limitations of cursors.

Seeking specialized PostgreSQL guidance? Get specialized advice for effective data management, query tuning, and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.

Subscribe to our Newsletters and Stay tuned for more interesting topics.

Contact Us Today!