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.