Performing operations on cursors in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of FETCH with SCROLL cursor

Consider the following PostgreSQL commands executed in order:

BEGIN;
DECLARE mycursor SCROLL CURSOR FOR SELECT generate_series(1,5);
FETCH ABSOLUTE 3 FROM mycursor;

What is the output of the FETCH command?

PostgreSQL

BEGIN;
DECLARE mycursor SCROLL CURSOR FOR SELECT generate_series(1,5);
FETCH ABSOLUTE 3 FROM mycursor;

A 3

B 1

C 5

D NULL

💡 Hint

The ABSOLUTE option fetches the row at the exact position in the result set.

The cursor selects numbers 1 to 5. FETCH ABSOLUTE 3 returns the third row, which is 3.

📝 Syntax

intermediate

Identify the syntax error in cursor declaration

Which of the following cursor declarations is syntactically correct in PostgreSQL?

A DECLARE cur1 CURSOR IN SELECT * FROM users;

B DECLARE cur1 CURSOR SELECT * FROM users;

C DECLARE cur1 CURSOR FOR (SELECT * FROM users);

D DECLARE cur1 CURSOR FOR SELECT * FROM users;

💡 Hint

Cursor declaration requires the keyword FOR before the query.

Option D correctly uses DECLARE cur1 CURSOR FOR followed by the SELECT statement. Other options miss FOR or use invalid syntax.

❓ optimization

advanced

Optimizing cursor usage for large datasets

You need to process a large table row by row using a cursor in PostgreSQL. Which option optimizes memory usage and avoids locking the entire table during processing?

A DECLARE cur NO SCROLL CURSOR WITH HOLD FOR SELECT * FROM large_table;

B DECLARE cur NO SCROLL CURSOR FOR SELECT * FROM large_table;

C DECLARE cur CURSOR FOR SELECT * FROM large_table;

D DECLARE cur SCROLL CURSOR WITH HOLD FOR SELECT * FROM large_table;

💡 Hint

WITH HOLD allows cursor to remain open after commit, NO SCROLL reduces overhead.

Option B uses NO SCROLL to reduce overhead and avoids locking by not using WITH HOLD, which keeps the cursor tied to the transaction. WITH HOLD cursors remain open after commit but can hold locks longer.

🔧 Debug

advanced

Debugging cursor fetch error

Given the following commands:

BEGIN;
DECLARE mycur CURSOR FOR SELECT id FROM products WHERE price > 100;
FETCH NEXT FROM mycur;
FETCH PRIOR FROM mycur;

What error will the last FETCH cause?

A ERROR: cursor is not scrollable

B ERROR: no prior row found

C ERROR: cursor does not exist

D No error, returns previous row

💡 Hint

By default, cursors are not scrollable unless declared SCROLL.

The cursor was declared without SCROLL, so FETCH PRIOR causes an error because the cursor cannot move backward.

🧠 Conceptual

expert

Understanding cursor behavior with transaction commits

In PostgreSQL, what happens to a cursor declared WITHOUT the WITH HOLD option when the transaction that declared it commits?

A The cursor resets to the first row but remains open

B The cursor remains open and usable in subsequent transactions

C The cursor is automatically closed and cannot be used further

D The cursor switches to read-only mode

💡 Hint

WITH HOLD controls cursor persistence after commit.

Cursors declared without WITH HOLD close automatically at transaction commit and cannot be used afterward.