HexaCluster Logo

Data Navigation with PostgreSQL Cursors – Part II

Welcome back to the second article in our series exploring PostgreSQL cursors. In the first part of this series, we explored the fundamentals of scrollable cursors. In this article, we’ll shift our focus to non-scrollable cursors and explore how they can enhance your data navigation experience with PostgreSQL Cursors.

Understanding Non-Scrollable Cursors

Non-scrollable cursors in PostgreSQL are similar to their scrollable counterparts, but with one significant difference. They only allow fetching rows in the forward direction. Once a row is fetched, you cannot go back to a previous row using these cursors.

data-navigation-with-postgresql-cursors-part-ii

Let’s dive into a practical example to better understand how non-scrollable cursors work.

Setting Up Your Environment

Make sure you have PostgreSQL installed and running on your machine. You can use tools like pgAdmin or connect to PostgreSQL through the command line.

For this example, we’ll create a simple table called employees with columns id, name, and salary.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary BIGINT
);

INSERT INTO employees (name, salary) VALUES
    ('Alice', 50000),
    ('Bob', 60000),
    ('Charlie', 75000);

Creating a Non-Scrollable Cursor

To create a non-scrollable cursor, you use the DECLARE statement with the NO SCROLL option. Let’s declare a non-scrollable cursor named employee_cursor.

DECLARE employee_cursor NO SCROLL CURSOR FOR SELECT * FROM employees;

Fetching Rows with a Non-Scrollable Cursor

Now that we have our cursor, let’s fetch rows from the employees table using the FETCH statement. Remember, we can only move forward.

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor;

If you are looking for any assistance around Migrations to PostgreSQL, click here for a Free Migration Assessment and eliminate your database software license costs with our support during migrations from Oracle to PostgreSQL and SQL Server to PostgreSQL.

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

This fetches the first row from the result set. You can continue fetching subsequent rows until there are no more rows to retrieve.

FETCH NEXT FROM employee_cursor;
FETCH NEXT FROM employee_cursor;

I tried using FETCH PRIOR with non-scrollable cursors on purpose to show their limitations, but it resulted in an error, highlighting that these cursors can only move forward in PostgreSQL.

FETCH PRIOR FROM employee_cursor;

ERROR:  cursor can only scan forward
HINT:  Declare it with SCROLL option to enable backward scan.

Let’s create an anonymous block to declare a cursor that moves only forward, then loop through and print the rows of the table.

DO $$
DECLARE
    employee_record employees%ROWTYPE;
    employee_cursor NO SCROLL CURSOR FOR SELECT * FROM employees;
BEGIN
    OPEN employee_cursor;

    LOOP
        FETCH NEXT FROM employee_cursor INTO employee_record;
            EXIT WHEN NOT FOUND;

        RAISE NOTICE 'Employee: %', employee_record; -- Prints every row of employees table
    END LOOP;

    CLOSE employee_cursor;
END $$;

Closing the Cursor

Always remember to close the cursor when you’re done to release resources.

CLOSE employee_cursor;

Conclusion

Non-scrollable cursors provide a straightforward way to navigate through result sets in a forward-only manner. Use them when you only need to process rows sequentially and don’t require the ability to move backward.

In the next part of this series, we’ll explore more advanced cursor features in PostgreSQL like With-Hold Cursors. Stay tuned for an in-depth look at how you can optimize your data navigation strategies.

Seeking specialized PostgreSQL support ? 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.