Data Navigation with PostgreSQL Cursors – Part III

Welcome back to our series on PostgreSQL Cursors. As we continue exploring PostgreSQL Cursors, including Scrollable and Non-Scrollable Cursors, we're now focusing on With-Hold Cursors in this article. With-Hold Cursors in PostgreSQL provide a crucial mechanism for managing data, enabling users to maintain query result sets even beyond transaction commitments.

Data Navigation with PostgreSQL Cursors.

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

What are With-Hold Cursors ?

With-Hold Cursors in PostgreSQL are cursor types that enable users to retain the query result set even after the transaction in which they were declared has been committed. Unlike other cursor types, which are automatically closed at the end of a transaction, With-Hold Cursors persist beyond transaction boundaries. This persistence allows for further processing or retrieval until explicitly closed or the session terminates. Consequently, users can maintain the cursor's data for extended periods, facilitating iterative or interactive data manipulation tasks across transactions.

How to Use a With-Hold Cursor ?

Using a With-Hold Cursor is similar to using other types of cursors in PostgreSQL. To illustrate the practical use of With-Hold Cursors, let's consider an example

DECLARE cursor_name CURSOR WITH HOLD FOR SELECT * FROM table_name;

Here:

• cursor_name is the name you give to your cursor.
• WITH HOLD tells PostgreSQL to keep the data even after you finish.
• SELECT statement gets the data you want to work with.

Using a With-Hold Cursor

Imagine we have a list of employees in a table, and we want to work with their data using a With-Hold Cursor. Let us see how we can declare such a cursor and fetch data from the cursor.

-- Declare the With-Hold Cursor
DECLARE emp_cursor CURSOR WITH HOLD FOR SELECT * FROM employees;

-- Fetch data from the Cursor
FETCH NEXT FROM emp_cursor;

Note: We must close the WITH HOLD CURSOR because it won't automatically close.

CLOSE emp_cursor;

With-Hold Cursors Across Transactions

Now, let's explore how With-Hold Cursors maintain their accessibility across different transactions within the same session.
Let us consider a case where we have a Transaction A. We declare a With-Hold Cursor to fetch data from the `employees` table inside this transaction.

-- Transaction A
BEGIN;

-- Declare a With-Hold Cursor
DECLARE emp_cursor CURSOR WITH HOLD FOR SELECT * FROM employees;

-- Fetch data from the Cursor
FETCH NEXT FROM emp_cursor;

-- Transaction A ends
COMMIT;

Now, even after Transaction A has ended, we can still access the With-Hold Cursor from Transaction B, as long as it is from the same session as the Transaction A.

-- Transaction B
BEGIN;

-- Fetch data from the With-Hold Cursor declared in Transaction A
FETCH NEXT FROM emp_cursor;

-- Transaction B ends
COMMIT;

This demonstrates that With-Hold Cursors can be accessed within the same session, from any transaction, providing persistence across transaction boundaries.

Note: Please note that With-Hold Cursors in PostgreSQL cannot be accessed once the session is closed. It's essential to ensure that you have completed all necessary operations with the cursor before ending your session to avoid losing access to the cursor data.

Conclusion

In conclusion, With-Hold Cursors offer significant advantages for data management in PostgreSQL. With-Hold Cursors are useful in PostgreSQL for keeping your data accessible throughout all transactions of a session. By understanding how to use them, you can make your work more efficient and flexible.

That wraps up our series on PostgreSQL Cursors. We hope you found these articles on Scrollable, Non-Scrollable and With-Hold cursors helpful.

Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.

Stay tuned for more interesting topics!

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