PostgreSQL – Cursor

A Cursor in PostgreSQL is used to process large tables. Suppose if a table has 10 million or billion rows. While performing a SELECT operation on the table it will take some time to process the result and most likely give an “out of memory” error and the program will be terminated.

A Cursor can only be declared inside a transaction. The cursor does not calculate the data but only prepares the query so that your data can be created when FETCH is called. In the end, simply commit the transaction.

Syntax:

DECLARE 
    [cursor_name] CURSOR FOR [query]

Let’s analyze the above syntax:

• Use DECLARE to declare a cursor
• [cursor_name] – Give any name to the cursor
• [query] – Give a query to the 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.

Syntax:

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

where direction can be empty,
number of rows you want or one of the following:

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

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 data into students table as follows:

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);

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

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:

COMMIT;

Commit the transaction at the end.

My Personal Notes arrow_drop_up

Save