Performing operations on cursors in PostgreSQL - Step-by-Step Execution

Concept Flow - Performing operations on cursors

Declare Cursor

↓

Open Cursor

↓

Fetch Rows

↓

Process Rows

↓

Repeat Fetch until no rows

↓

Close Cursor

↓

End

This flow shows how a cursor is declared, opened, rows are fetched and processed repeatedly until no more rows remain, then the cursor is closed.

Execution Sample

PostgreSQL

DECLARE cur CURSOR FOR SELECT id, name FROM users;
OPEN cur;
FETCH NEXT FROM cur INTO v_id, v_name;
-- process data
CLOSE cur;

This code declares a cursor for a query, opens it, fetches one row into variables, processes it, and then closes the cursor.

Execution Table

Step	Action	Cursor State	Variables (v_id, v_name)	Output/Result
1	DECLARE cursor for SELECT id, name FROM users	Declared but closed	NULL, NULL	Cursor ready for use
2	OPEN cursor	Open, positioned before first row	NULL, NULL	Cursor ready to fetch
3	FETCH NEXT INTO v_id, v_name	Open, positioned on first row	1, 'Alice'	Fetched first row
4	Process row	Open, positioned on first row	1, 'Alice'	Processed row with id=1
5	FETCH NEXT INTO v_id, v_name	Open, positioned on second row	2, 'Bob'	Fetched second row
6	Process row	Open, positioned on second row	2, 'Bob'	Processed row with id=2
7	FETCH NEXT INTO v_id, v_name	Open, positioned on third row	3, 'Carol'	Fetched third row
8	Process row	Open, positioned on third row	3, 'Carol'	Processed row with id=3
9	FETCH NEXT INTO v_id, v_name	Open, positioned after last row	NULL, NULL	No more rows, fetch returns no data
10	CLOSE cursor	Closed	NULL, NULL	Cursor closed, resources freed

💡 Fetch returns no data at step 9, so loop ends and cursor is closed at step 10.

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 7	After Step 9	Final
v_id	NULL	1	2	3	NULL	NULL
v_name	NULL	'Alice'	'Bob'	'Carol'	NULL	NULL

Key Moments - 3 Insights

Why do v_id and v_name become NULL after the last fetch?

At step 9 in the execution_table, the FETCH returns no data because the cursor reached the end of the result set, so variables are set to NULL indicating no more rows.

What happens if we try to FETCH before OPENing the cursor?

You cannot fetch before opening the cursor; the cursor must be OPEN to position it on the result set. The execution_table starts with OPEN at step 2 before FETCH at step 3.

Why do we need to CLOSE the cursor?

Closing the cursor at step 10 releases database resources. Leaving cursors open can cause resource leaks and lock issues.

Visual Quiz - 3 Questions

Look at the execution_table, what are the values of v_id and v_name after step 5?

A v_id=3, v_name='Carol'

B v_id=1, v_name='Alice'

C v_id=2, v_name='Bob'

D v_id=NULL, v_name=NULL

💡 Hint

Check the 'Variables' column at step 5 in the execution_table.

At which step does the FETCH return no more rows?

A Step 7

B Step 9

C Step 10

D Step 3

💡 Hint

Look for the step where variables become NULL and output says 'No more rows'.

If we forget to CLOSE the cursor, what is the likely effect?

A Database resources remain allocated, causing potential issues

B Cursor fetches rows twice

C Cursor automatically closes after last fetch

D Variables reset to NULL automatically

💡 Hint

Refer to the key_moments explanation about closing cursors.

Concept Snapshot

DECLARE cursor FOR query;
OPEN cursor;
FETCH NEXT FROM cursor INTO variables;
Repeat FETCH and process rows until no more rows;
CLOSE cursor to free resources.
Cursors allow row-by-row processing of query results.

Full Transcript

This visual execution shows how to perform operations on cursors in PostgreSQL. First, a cursor is declared for a SELECT query. Then it is opened to prepare for fetching rows. Each FETCH moves the cursor to the next row and loads data into variables. After processing each row, FETCH repeats until no rows remain, indicated by variables becoming NULL. Finally, the cursor is closed to release resources. This step-by-step trace helps beginners understand cursor lifecycle and variable changes during execution.

Practice

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

easy

A. To process query results one row at a time

B. To speed up query execution by parallel processing

C. To permanently store query results in a table

D. To automatically optimize query plans

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

   Only To process query results one row at a time correctly describes this row-wise processing purpose.

3. Final Answer:

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

4. Quick Check:

   Cursor = row-by-row processing [OK]

Hint: Cursors process rows one by one, not all at once [OK]

Common Mistakes:

• Thinking cursors speed up queries automatically
• Confusing cursors with temporary tables
• Assuming cursors optimize query plans

2. Which of the following is the correct syntax to declare a cursor named cur1 for a SELECT query in PostgreSQL?

easy

A. OPEN cur1 CURSOR FOR SELECT * FROM employees;

B. DECLARE cur1 CURSOR FOR SELECT * FROM employees;

C. FETCH cur1 CURSOR FOR SELECT * FROM employees;

D. CREATE CURSOR cur1 FOR SELECT * FROM employees;

Solution

1. Step 1: Recall cursor declaration syntax

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

2. Step 2: Match syntax with options

   DECLARE cur1 CURSOR FOR SELECT * FROM employees; matches the correct DECLARE syntax; others misuse OPEN, FETCH, or CREATE.

3. Final Answer:

   DECLARE cur1 CURSOR FOR SELECT * FROM employees; -> Option B

4. Quick Check:

   DECLARE cursor FOR query [OK]

Hint: Use DECLARE to define cursor before OPEN or FETCH [OK]

Common Mistakes:

• Using OPEN instead of DECLARE to define cursor
• Trying to FETCH during declaration
• Using CREATE CURSOR which is invalid

3. Consider this PostgreSQL code snippet:

DECLARE cur CURSOR FOR SELECT id FROM users ORDER BY id;
OPEN cur;
FETCH NEXT FROM cur;
FETCH NEXT FROM cur;
CLOSE cur;

What will be the output of the two FETCH commands if the users table has ids 10, 20, 30 in ascending order?

medium

A. First FETCH returns 10, second FETCH returns 20

B. First FETCH returns 20, second FETCH returns 30

C. Both FETCH commands return 10

D. First FETCH returns 30, second FETCH returns NULL

Solution

1. Step 1: Understand cursor order and FETCH

   The cursor selects ids ordered by id: 10, 20, 30. FETCH NEXT returns rows sequentially.

2. Step 2: Trace FETCH commands

   First FETCH returns the first row: 10. Second FETCH returns the next row: 20.

3. Final Answer:

   First FETCH returns 10, second FETCH returns 20 -> Option A

4. Quick Check:

   FETCH NEXT returns rows in order [OK]

Hint: FETCH NEXT returns rows in declared order one by one [OK]

Common Mistakes:

• Assuming FETCH skips rows
• Confusing FETCH NEXT with FETCH ALL
• Expecting FETCH to return NULL before end

4. Given this code snippet:

DECLARE cur CURSOR FOR SELECT name FROM products;
OPEN cur;
FETCH cur;
CLOSE cur;
FETCH cur;

What error will occur when running this code?

medium

A. ERROR: cursor "cur" does not exist

B. No error, FETCH returns next row

C. ERROR: syntax error near FETCH

D. ERROR: cursor "cur" is not open

Solution

1. Step 1: Analyze cursor lifecycle

   Cursor is declared and opened, then FETCH is called once, then cursor is closed.

2. Step 2: Identify error on second FETCH

   After CLOSE, cursor is not open, so FETCH causes "cursor is not open" error.

3. Final Answer:

   ERROR: cursor "cur" is not open -> Option D

4. Quick Check:

   FETCH after CLOSE causes 'not open' error [OK]

Hint: Cannot FETCH after CLOSE; cursor must be open [OK]

Common Mistakes:

• Trying to FETCH after cursor is closed
• Expecting FETCH to reopen cursor automatically
• Confusing 'not open' with 'does not exist' error

5. You want to process a large table orders row by row using a cursor in a PL/pgSQL function. Which sequence of commands correctly opens the cursor, fetches all rows one by one, and closes it after processing?

hard

A. DECLARE cur CURSOR FOR SELECT * FROM orders; OPEN cur; FETCH ALL FROM cur; CLOSE cur;

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

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

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

Solution

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

   Declare cursor, open it, then loop fetching rows until no more rows (EXIT WHEN NOT FOUND), then close cursor.

2. Step 2: Evaluate options

   DECLARE cur CURSOR FOR SELECT * FROM orders; OPEN cur; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur; correctly shows DECLARE, OPEN, LOOP with FETCH and EXIT, then CLOSE. Others misuse order or FETCH ALL which fetches all rows at once.

3. Final Answer:

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

4. Quick Check:

   Cursor loop with FETCH and EXIT WHEN NOT FOUND [OK]

Hint: Use LOOP with FETCH and EXIT WHEN NOT FOUND to process cursor rows [OK]

Common Mistakes:

• Opening cursor after FETCH
• Using FETCH ALL instead of looping FETCH
• Closing cursor before processing all rows