Cursor declaration and usage in PostgreSQL - Mini Project: Build & Apply

Cursor Declaration and Usage in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to process each book record one by one to check stock levels and prepare for restocking.

🎯 Goal: Build a PostgreSQL script that declares a cursor to select books with low stock, fetches each record one at a time, and processes them.

📋 What You'll Learn

Create a cursor named book_cursor selecting id, title, and stock from the books table where stock is less than 5

Declare an integer variable low_stock_threshold with value 5

Use a LOOP to fetch each row from book_cursor into variables book_id, book_title, and book_stock

Exit the loop when no more rows are found

Close the cursor after processing

💡 Why This Matters

🌍 Real World

Cursors help process large query results row by row, useful in inventory management or batch processing.

💼 Career

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

1

Declare the cursor

Write a PostgreSQL DECLARE statement to create a cursor named book_cursor that selects id, title, and stock from the books table where stock < 5.

PostgreSQL

-- Declare the cursor named book_cursor
-- Your code here

Need a hint?

Use DECLARE cursor_name CURSOR FOR SELECT ... syntax.

2

Declare the threshold variable

Declare an integer variable named low_stock_threshold and set it to 5.

PostgreSQL

DECLARE book_cursor CURSOR FOR SELECT id, title, stock FROM books WHERE stock < 5;
-- Declare low_stock_threshold variable
-- Your code here

Need a hint?

Use variable_name TYPE := value; to declare and initialize.

3

Fetch rows using a loop

Write a LOOP that fetches each row from book_cursor into variables book_id, book_title, and book_stock. Exit the loop when no more rows are found.

PostgreSQL

DECLARE book_cursor CURSOR FOR SELECT id, title, stock FROM books WHERE stock < 5;
low_stock_threshold INTEGER := 5;

-- Start loop to fetch rows
-- Your code here

Need a hint?

Use FETCH cursor_name INTO variables; and EXIT WHEN NOT FOUND; inside the loop.

4

Close the cursor

Add a statement to close the cursor named book_cursor after the loop.

PostgreSQL

DECLARE book_cursor CURSOR FOR SELECT id, title, stock FROM books WHERE stock < 5;
low_stock_threshold INTEGER := 5;

LOOP
    FETCH book_cursor INTO book_id, book_title, book_stock;
    EXIT WHEN NOT FOUND;
    -- Processing logic can go here
END LOOP;

-- Close the cursor
-- Your code here

Need a hint?

Use CLOSE cursor_name; to release the cursor.