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.

Data Navigation with PostgreSQL Cursors.

• Scrollable Cursors
• Non-Scrollable Cursors
• With-Hold 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.

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.

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.

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