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.