Cursor declaration and usage in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of FETCH with declared cursor

Given the following cursor declaration and fetch commands, what will be the output of the FETCH command?

PostgreSQL

DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees ORDER BY id;
FETCH NEXT FROM emp_cursor;

A Returns no rows because cursor is not opened

B Returns all rows from employees ordered by id

C Syntax error due to missing OPEN statement

D Returns the first row with columns id and name from employees ordered by id

💡 Hint

Remember that DECLARE only defines the cursor, FETCH retrieves rows.

In PostgreSQL, DECLARE defines the cursor but it is not automatically opened outside of a transaction block. FETCH NEXT without opening the cursor will return no rows or cause an error.

📝 Syntax

intermediate

Identify the syntax error in cursor usage

Which option contains a syntax error when declaring and using a cursor in PostgreSQL?

A

DECLARE my_cursor CURSOR FOR SELECT * FROM products;
OPEN my_cursor;
FETCH NEXT my_cursor;
CLOSE my_cursor;

B

DECLARE my_cursor CURSOR FOR SELECT * FROM products;
OPEN my_cursor;
FETCH NEXT FROM my_cursor;
CLOSE my_cursor;

C

DECLARE my_cursor CURSOR FOR SELECT * FROM products;
FETCH NEXT FROM my_cursor;
CLOSE my_cursor;

D

OPEN my_cursor;
DECLARE my_cursor CURSOR FOR SELECT * FROM products;
FETCH NEXT FROM my_cursor;
CLOSE my_cursor;

💡 Hint

Cursor must be declared before it is opened.

In PostgreSQL, DECLARE must come before OPEN. Option D tries to OPEN before DECLARE, causing a syntax error.

❓ optimization

advanced

Optimizing cursor usage for large data sets

Which option best optimizes cursor usage to reduce memory consumption when processing a large table in PostgreSQL?

A Declare the cursor as SCROLL to allow backward and forward movement

B Declare the cursor WITH HOLD to keep it open after commit

C Declare the cursor WITHOUT HOLD and fetch rows in small batches

D Declare the cursor as INSENSITIVE to avoid changes during fetch

💡 Hint

Fetching in small batches helps manage memory better.

Declaring the cursor WITHOUT HOLD and fetching rows in small batches reduces memory usage because it processes limited rows at a time and closes after transaction.

🧠 Conceptual

advanced

Understanding cursor behavior after transaction commit

What happens to a cursor declared WITHOUT HOLD after the transaction that opened it commits in PostgreSQL?

A The cursor is automatically closed and cannot be fetched from after commit

B The cursor remains open and can be fetched from after commit

C The cursor changes to a HOLD cursor automatically

D The cursor's position resets to the first row after commit

💡 Hint

Think about cursor lifespan related to transactions.

Cursors declared WITHOUT HOLD are closed automatically when the transaction commits, so they cannot be used afterward.

🔧 Debug

expert

Debugging cursor fetch error

Given this code snippet, what error will occur when running the FETCH statement? BEGIN; DECLARE cur CURSOR FOR SELECT id FROM users WHERE active = true; OPEN cur; CLOSE cur; FETCH NEXT FROM cur; COMMIT;

PostgreSQL

BEGIN;
DECLARE cur CURSOR FOR SELECT id FROM users WHERE active = true;
OPEN cur;
CLOSE cur;
FETCH NEXT FROM cur;
COMMIT;

A FETCH will return no rows but no error

B FETCH will raise a 'cursor is not open' error

C FETCH will return the first active user id

D FETCH will raise a syntax error

💡 Hint

Consider what happens after closing a cursor.

After closing the cursor, trying to FETCH from it causes a 'cursor is not open' error.

Practice

1. What is the primary purpose of declaring a cursor in PostgreSQL?

easy

A. To speed up bulk inserts

B. To process query results one row at a time

C. To create a new table in the database

D. To backup the database automatically

Solution

1. Step 1: Understand what a cursor does

   A cursor allows you to handle query results row by row instead of all at once.

2. Step 2: Compare options with cursor purpose

   Only To process query results one row at a time describes this behavior; others describe unrelated tasks.

3. Final Answer:

   To process query results one row at a time -> Option B

4. Quick Check:

   Cursor purpose = process rows one by one [OK]

Hint: Cursors handle rows stepwise, not bulk operations [OK]

Common Mistakes:

• Confusing cursors with table creation
• Thinking cursors speed up inserts
• Assuming cursors automate backups

2. Which of the following is the correct syntax to declare a cursor named cur_emp for selecting all rows from employees table?

easy

A. CREATE CURSOR cur_emp AS SELECT * FROM employees;

B. OPEN cur_emp CURSOR SELECT * FROM employees;

C. DECLARE cur_emp CURSOR FOR SELECT * FROM employees;

D. FETCH cur_emp FROM employees;

Solution

1. Step 1: Recall cursor declaration syntax

   In PostgreSQL, cursors are declared with DECLARE cursor_name CURSOR FOR query.

2. Step 2: Match syntax with options

   DECLARE cur_emp CURSOR FOR SELECT * FROM employees; matches this exactly; others use incorrect keywords or order.

3. Final Answer:

   DECLARE cur_emp CURSOR FOR SELECT * FROM employees; -> Option C

4. Quick Check:

   DECLARE + CURSOR + FOR + query = correct syntax [OK]

Hint: Use DECLARE ... CURSOR FOR ... to declare [OK]

Common Mistakes:

• Using OPEN instead of DECLARE for declaration
• Confusing FETCH with DECLARE
• Using CREATE CURSOR which is invalid syntax

3. Given the following code snippet, what will be the output after fetching from the cursor?

DECLARE cur_emp CURSOR FOR SELECT id FROM employees ORDER BY id LIMIT 3;
OPEN cur_emp;
FETCH NEXT FROM cur_emp;
FETCH NEXT FROM cur_emp;

medium

A. First two employee ids in ascending order

B. All employee ids from the table

C. Syntax error due to missing CLOSE statement

D. Empty result because cursor is not opened

Solution

1. Step 1: Understand cursor declaration and fetch

   The cursor selects 3 employee ids ordered ascending. FETCH NEXT retrieves one row each time.

2. Step 2: Analyze fetch calls

   Two FETCH NEXT calls return the first two rows from the cursor result.

3. Final Answer:

   First two employee ids in ascending order -> Option A

4. Quick Check:

   Two FETCH NEXT = two rows fetched [OK]

Hint: Each FETCH returns one row in cursor order [OK]

Common Mistakes:

• Assuming FETCH returns all rows at once
• Thinking missing CLOSE causes syntax error
• Believing cursor must be closed before fetching

4. Identify the error in the following cursor usage:

DECLARE cur_dept CURSOR FOR SELECT name FROM departments;
FETCH NEXT FROM cur_dept;
OPEN cur_dept;
CLOSE cur_dept;

medium

A. Cursor declaration syntax is incorrect

B. Cursor is declared after fetching

C. Cursor is closed before declaration

D. Cursor is fetched before it is opened

Solution

1. Step 1: Check the order of cursor operations

   Cursors must be declared, then opened, then fetched, then closed.

2. Step 2: Identify incorrect sequence

   Here, FETCH is called before OPEN, which is invalid.

3. Final Answer:

   Cursor is fetched before it is opened -> Option D

4. Quick Check:

   OPEN must precede FETCH [OK]

Hint: Always OPEN cursor before FETCH [OK]

Common Mistakes:

• Fetching before opening cursor
• Closing cursor before opening
• Misordering declaration and fetch

5. You want to process all rows from orders table one by one using a cursor in a PL/pgSQL function. Which sequence of statements correctly implements this?

hard

A. DECLARE cur_orders CURSOR FOR SELECT * FROM orders; OPEN cur_orders; LOOP FETCH cur_orders INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur_orders;

B. OPEN cur_orders; DECLARE cur_orders CURSOR FOR SELECT * FROM orders; FETCH cur_orders INTO rec; CLOSE cur_orders;

C. DECLARE cur_orders CURSOR FOR SELECT * FROM orders; FETCH cur_orders INTO rec; OPEN cur_orders; CLOSE cur_orders;

D. DECLARE cur_orders CURSOR FOR SELECT * FROM orders; OPEN cur_orders; FETCH ALL FROM cur_orders INTO rec; CLOSE cur_orders;

Solution

1. Step 1: Understand correct cursor usage in PL/pgSQL

   Declare cursor, open it, then loop fetching rows until no more rows, then close cursor.

2. Step 2: Analyze each option

   DECLARE cur_orders CURSOR FOR SELECT * FROM orders; OPEN cur_orders; LOOP FETCH cur_orders INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur_orders; follows correct order and uses LOOP with EXIT WHEN NOT FOUND to process all rows. Others have wrong order or invalid FETCH ALL.

3. Final Answer:

   DECLARE cur_orders CURSOR FOR SELECT * FROM orders; OPEN cur_orders; LOOP FETCH cur_orders INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur_orders; -> Option A

4. Quick Check:

   Declare, Open, Loop Fetch, Close = correct pattern [OK]

Hint: Use LOOP with FETCH and EXIT WHEN NOT FOUND [OK]

Common Mistakes:

• Opening cursor before declaring
• Fetching before opening
• Using FETCH ALL which is invalid for cursors