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.
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.
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;
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;
Commit the transaction at the end.
Please Login to comment...