Cursor declaration and usage in PostgreSQL - Step-by-Step Execution

Concept Flow - Cursor declaration and usage

Declare Cursor

↓

Open Cursor

↓

Fetch Row

↓

Process Row

↓

More Rows?

Yes→Fetch Row

No↓

Close Cursor

The cursor is declared, then opened to access query results row by row. Each row is fetched and processed until no more rows remain, then the cursor is closed.

Execution Sample

PostgreSQL

DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;
OPEN emp_cursor;
FETCH FROM emp_cursor INTO emp_id, emp_name;
-- process emp_id, emp_name
CLOSE emp_cursor;

This code declares a cursor to select employee ids and names, opens it, fetches one row, processes it, and then closes the cursor.

Execution Table

Step	Action	Cursor State	Fetched Data	Notes
1	DECLARE emp_cursor	Declared but not open	None	Cursor is ready but not yet opened
2	OPEN emp_cursor	Open and ready to fetch	None	Cursor points before first row
3	FETCH	Open	(1, 'Alice')	First row fetched into variables
4	Process row	Open	(1, 'Alice')	Use fetched data for processing
5	FETCH	Open	(2, 'Bob')	Second row fetched
6	Process row	Open	(2, 'Bob')	Process second row
7	FETCH	Open	(3, 'Carol')	Third row fetched
8	Process row	Open	(3, 'Carol')	Process third row
9	FETCH	Open	No more rows	Reached end of result set
10	CLOSE emp_cursor	Closed	None	Cursor closed, resources freed

💡 No more rows to fetch, cursor is closed

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 7	Final
emp_id	NULL	1	2	3	3
emp_name	NULL	Alice	Bob	Carol	Carol

Key Moments - 3 Insights

Why do we need to OPEN the cursor after DECLARE?

Declaring a cursor only defines it; opening it actually runs the query and prepares the result set for fetching, as shown between steps 1 and 2 in the execution_table.

What happens if we FETCH after all rows are processed?

The FETCH returns no data indicating the end of the result set, as seen in step 9 where 'No more rows' is fetched, signaling to stop fetching.

Why must we CLOSE the cursor?

Closing the cursor frees database resources and marks it as no longer usable, as shown in step 10 where the cursor state changes to 'Closed'.

Visual Quiz - 3 Questions

Look at the execution_table, what is the cursor state immediately after step 2?

A Declared but not open

B Open and ready to fetch

C Closed

D Fetching data

💡 Hint

Check the 'Cursor State' column for step 2 in the execution_table.

At which step does the FETCH return no more rows?

A Step 7

B Step 8

C Step 9

D Step 10

💡 Hint

Look for 'No more rows' in the 'Fetched Data' column in the execution_table.

If we skip the CLOSE step, what would happen to the cursor?

A It remains open and consumes resources

B It automatically closes after last fetch

C It deletes the data fetched

D It resets to the first row

💡 Hint

Refer to the 'Notes' in step 10 about closing the cursor.

Concept Snapshot

Cursor declaration and usage in PostgreSQL:
- DECLARE cursor_name CURSOR FOR query;
- OPEN cursor_name;
- FETCH FROM cursor_name INTO variables;
- Process fetched row(s);
- Repeat FETCH until no rows left;
- CLOSE cursor_name to free resources.

Full Transcript

In PostgreSQL, a cursor lets you handle query results one row at a time. First, you DECLARE the cursor with a query. Then you OPEN it to start fetching rows. Each FETCH gets the next row into variables for processing. When no rows remain, FETCH returns no data. Finally, you CLOSE the cursor to release resources. This step-by-step process helps manage large data sets efficiently.