HexaCluster Logo

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.

Article 1 : Scrollable Cursors
Article 2 : Non-Scrollable 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.

Data Navigation using PostgreSQL Cursors

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.

Free Oracle to PostgreSQL Migration Assessment
Interested in migrating from Oracle or SQL Server to PostgreSQL?
Sign up for our Free Migration Assessment!

- No cost evaluation
- Smooth transition to PostgreSQL.
Please enable JavaScript in your browser to complete this form.
Type of Database Migration

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.

Contact HexaCluster
Please enable JavaScript in your browser to complete this form.
Let us know which of these PostgreSQL Services can help you with today !
Let us know which of these Machine Learning Services we can help you with today !

Author

  • Jagadeesh Panuganti

    Jagadeesh Panuganti is currently working at HexaCluster as a Senior Developer. His areas of expertise include PostgreSQL and Oracle; he is particularly skilled in PostgreSQL development and has experience with challenging migrations from Oracle to PostgreSQL. He also has expertise using the MERN stack as a full-stack developer. Beyond database development, his skill set includes creating applications in C, Python and Java.

Add A Comment

We're glad you have chosen to leave a comment. Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Our PostgreSQL Services

Our Machine Learning Services

Subscribe to our Newsletters

Please enable JavaScript in your browser to complete this form.
I would like to receive Newsletters on following topics :
Enter your email and click on subscribe now, to subscribe to our free newsletters.

Share article

HexaCluster Logo

Get in Touch

© 2023 Company. All Rights Reserved.