PostgreSQL - Cursor

In the area of database management, effective data retrieval is essential particularly when handling large datasets. PostgreSQL offers the functionality of a cursor which allows for incremental data retrieval from extensive result sets.

By using PostgreSQL cursor syntax, developers can manage memory more efficiently and enhance application performance while processing rows one at a time. In this article, We will learn about the Cursor in PostgreSQL by understanding various examples and so on.

What is a Cursor in PostgreSQL?

• A cursor in PostgreSQL is a database object that enables traversal over the result set of a query. It acts as a pointer that allows us to fetch rows sequentially.
  
• PostgreSQL cursors are particularly useful when working with large tables containing millions of records where traditional SELECT operations may lead to performance issues or even out-of-memory errors.

Syntax of Declaring a Cursor

DECLARE cursor_name CURSOR FOR query;

Explanation:

• DECLARE: The keyword used to declare a cursor.
  
• cursor_name: The name assigned to the cursor.
  
• query: The SQL query associated with the cursor

How to Fetch Data from a Cursor?

After declaring a cursor, we can get the data using FETCH. The FETCH gets the next row(s) from the cursor. If no row found, then it returns NULL.

FETCH [direction (rows)] FROM [cursor_name];

Parameters:

• '[direction (rows)]': Specifies the direction and number of rows to fetch. If no direction is specified, the default is NEXT.
• '[cursor_name]': The name of the cursor from which to fetch data.

Directions for FETCH

• NEXT: Fetches the next rows.
  
• PRIOR: Fetches the previous rows.
  
• FIRST: Fetches the first rows.
  
• LAST: Fetches the last rows.
  
• ABSOLUTE count: Fetches the row at the absolute position 'count'.
  
• RELATIVE count: Fetches the row at the relative position 'count'.
  
• ALL: Fetches all remaining rows.
  
• FORWARD count: Fetches the next 'count' rows.
  
• BACKWARD count: Fetches the previous 'count' rows.

Steps to Use Cursors in PostgreSQL

Let us take a look at an example of Cursor in PostgreSQL to better understand the concept.

Step 1: Create a Sample Table

Lets, create a sample table using the below commands for examples:

CREATE TABLE students (
   student_id serial PRIMARY KEY,
   full_name VARCHAR NOT NULL,
   branch_id INT
);
INSERT INTO students (
    student_id,
    full_name,
    branch_id
)
VALUES
    (1, 'M.S Dhoni', NULL),
    (2, 'Sachin Tendulkar', 1),
    (3, 'R. Sharma', 1),
    (4, 'S. Raina', 1),
    (5, 'B. Kumar', 1),
    (6, 'Y. Singh', 2),
    (7, 'Virender Sehwag ', 2),
    (8, 'Ajinkya Rahane', 2),
    (9, 'Shikhar Dhawan', 2),
    (10, 'Mohammed Shami', 3),
    (11, 'Shreyas Iyer', 3),
    (12, 'Mayank Agarwal', 3),
    (13, 'K. L. Rahul', 3),
    (14, 'Hardik Pandya', 4),
    (15, 'Dinesh Karthik', 4),
    (16, 'Jasprit Bumrah', 7),
    (17, 'Kuldeep Yadav', 7),
    (18, 'Yuzvendra Chahal', 8),
    (19, 'Rishabh Pant', 8),
    (20, 'Sanju Samson', 8)

Step 2: Declare and Use a Cursor

Now that the table is ready we can declare our cursor.

Query:

BEGIN;
DECLARE 
    my_cursor CURSOR FOR SELECT * FROM students;

Fetch the data.

FETCH 10 FROM my_cursor;

Output:

FETCH PRIOR FROM my_cursor;
FETCH PRIOR FROM my_cursor;

The above query will give you row 9 and 8 since right now our cursor is at 10;

FETCH 6 FROM my_cursor;

Output:

Step 3: Commit the Transaction

Commit the transaction at the end.

COMMIT;

Important Points About Cursor in PostgreSQL

• Cursors must be declared within a transaction block using BEGIN and COMMIT. Outside of a transaction, the cursor's scope is limited to the session.
  
• By default, cursors in PostgreSQL are scrollable, meaning you can fetch rows in any direction (NEXT, PRIOR, FIRST, LAST). Non-scrollable cursors can only move forward and are declared with 'NO SCROLL'.
  
• PostgreSQL supports different types of cursors like BINARY and INSENSITIVE. The default cursor is INSENSITIVE, meaning it does not reflect changes made after the cursor is opened.
  
• Fetching data in smaller chunks (e.g., 'FETCH 100 FROM my_cursor') can improve performance by reducing memory consumption and processing time.

Conclusion

Overall, utilizing cursors in PostgreSQL can improve the efficiency of your database interactions. By mastering how to declare a cursor in PostgreSQL and fetch data from a PostgreSQL cursor, you can streamline operations on large tables without compromising system resources. Adopting cursors will enable you to optimize performance and provide a smoother data handling experience in your applications.