Performing operations on cursors in PostgreSQL - Mini Project: Build & Apply

Performing Operations on Cursors in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to read through the list of books one by one to perform some operations, like checking stock or updating prices. Using cursors helps you handle the data row by row, just like flipping through pages of a book.

🎯 Goal: Build a PostgreSQL script that declares a cursor to select all books, fetches rows one at a time, and closes the cursor properly.

📋 What You'll Learn

Create a cursor named book_cursor that selects all columns from the books table.

Declare a variable book_record to hold each row fetched from the cursor.

Fetch one row at a time from book_cursor into book_record.

Close the cursor book_cursor after fetching.

💡 Why This Matters

🌍 Real World

Cursors are useful when you want to process large query results row by row, such as updating records or generating reports without loading all data at once.

💼 Career

Database developers and administrators use cursors to handle complex data processing tasks efficiently in enterprise applications.

1

Declare a cursor for the books table

Write a PostgreSQL block that declares a cursor named book_cursor for the query SELECT * FROM books.

PostgreSQL

-- Declare the cursor named book_cursor for selecting all from books
-- Your code here

Need a hint?

Use DECLARE to create the cursor and OPEN it inside the block.

2

Declare a variable to hold fetched rows

Inside the same block, declare a variable named book_record of type books%ROWTYPE to hold each row fetched from the cursor.

PostgreSQL

DO $$
DECLARE
    book_cursor CURSOR FOR SELECT * FROM books;
    -- Declare book_record variable here
    -- Your code here
BEGIN
    OPEN book_cursor;
END $$;

Need a hint?

Use variable_name table_name%ROWTYPE to declare a variable matching a table row.

3

Fetch one row from the cursor

Add a FETCH statement inside the block to fetch one row from book_cursor into the variable book_record.

PostgreSQL

DO $$
DECLARE
    book_cursor CURSOR FOR SELECT * FROM books;
    book_record books%ROWTYPE;
BEGIN
    OPEN book_cursor;
    -- Fetch one row from book_cursor into book_record
    -- Your code here
END $$;

Need a hint?

Use FETCH cursor_name INTO variable_name to get one row.

4

Close the cursor after fetching

Add a CLOSE statement to close the cursor book_cursor after fetching the row.

PostgreSQL

DO $$
DECLARE
    book_cursor CURSOR FOR SELECT * FROM books;
    book_record books%ROWTYPE;
BEGIN
    OPEN book_cursor;
    FETCH book_cursor INTO book_record;
    -- Close the cursor book_cursor
    -- Your code here
END $$;

Need a hint?

Always close your cursor with CLOSE cursor_name when done.