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.

Practice

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

easy

A. To speed up bulk inserts

B. To process query results one row at a time

C. To create a new table in the database

D. To backup the database automatically

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 with cursor purpose

   Only To process query results one row at a time describes this behavior; others describe unrelated tasks.

3. Final Answer:

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

4. Quick Check:

   Cursor purpose = process rows one by one [OK]

Hint: Cursors handle rows stepwise, not bulk operations [OK]

Common Mistakes:

• Confusing cursors with table creation
• Thinking cursors speed up inserts
• Assuming cursors automate backups

2. Which of the following is the correct syntax to declare a cursor named cur_emp for selecting all rows from employees table?

easy

A. CREATE CURSOR cur_emp AS SELECT * FROM employees;

B. OPEN cur_emp CURSOR SELECT * FROM employees;

C. DECLARE cur_emp CURSOR FOR SELECT * FROM employees;

D. FETCH cur_emp FROM employees;

Solution

1. Step 1: Recall cursor declaration syntax

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

2. Step 2: Match syntax with options

   DECLARE cur_emp CURSOR FOR SELECT * FROM employees; matches this exactly; others use incorrect keywords or order.

3. Final Answer:

   DECLARE cur_emp CURSOR FOR SELECT * FROM employees; -> Option C

4. Quick Check:

   DECLARE + CURSOR + FOR + query = correct syntax [OK]

Hint: Use DECLARE ... CURSOR FOR ... to declare [OK]

Common Mistakes:

• Using OPEN instead of DECLARE for declaration
• Confusing FETCH with DECLARE
• Using CREATE CURSOR which is invalid syntax

3. Given the following code snippet, what will be the output after fetching from the cursor?

DECLARE cur_emp CURSOR FOR SELECT id FROM employees ORDER BY id LIMIT 3;
OPEN cur_emp;
FETCH NEXT FROM cur_emp;
FETCH NEXT FROM cur_emp;

medium

A. First two employee ids in ascending order

B. All employee ids from the table

C. Syntax error due to missing CLOSE statement

D. Empty result because cursor is not opened

Solution

1. Step 1: Understand cursor declaration and fetch

   The cursor selects 3 employee ids ordered ascending. FETCH NEXT retrieves one row each time.

2. Step 2: Analyze fetch calls

   Two FETCH NEXT calls return the first two rows from the cursor result.

3. Final Answer:

   First two employee ids in ascending order -> Option A

4. Quick Check:

   Two FETCH NEXT = two rows fetched [OK]

Hint: Each FETCH returns one row in cursor order [OK]

Common Mistakes:

• Assuming FETCH returns all rows at once
• Thinking missing CLOSE causes syntax error
• Believing cursor must be closed before fetching

4. Identify the error in the following cursor usage:

DECLARE cur_dept CURSOR FOR SELECT name FROM departments;
FETCH NEXT FROM cur_dept;
OPEN cur_dept;
CLOSE cur_dept;

medium

A. Cursor declaration syntax is incorrect

B. Cursor is declared after fetching

C. Cursor is closed before declaration

D. Cursor is fetched before it is opened

Solution

1. Step 1: Check the order of cursor operations

   Cursors must be declared, then opened, then fetched, then closed.

2. Step 2: Identify incorrect sequence

   Here, FETCH is called before OPEN, which is invalid.

3. Final Answer:

   Cursor is fetched before it is opened -> Option D

4. Quick Check:

   OPEN must precede FETCH [OK]

Hint: Always OPEN cursor before FETCH [OK]

Common Mistakes:

• Fetching before opening cursor
• Closing cursor before opening
• Misordering declaration and fetch

5. You want to process all rows from orders table one by one using a cursor in a PL/pgSQL function. Which sequence of statements correctly implements this?

hard

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

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

C. DECLARE cur_orders CURSOR FOR SELECT * FROM orders; FETCH cur_orders INTO rec; OPEN cur_orders; CLOSE cur_orders;

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

Solution

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

   Declare cursor, open it, then loop fetching rows until no more rows, then close cursor.

2. Step 2: Analyze each option

   DECLARE cur_orders CURSOR FOR SELECT * FROM orders; OPEN cur_orders; LOOP FETCH cur_orders INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur_orders; follows correct order and uses LOOP with EXIT WHEN NOT FOUND to process all rows. Others have wrong order or invalid FETCH ALL.

3. Final Answer:

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

4. Quick Check:

   Declare, Open, Loop Fetch, Close = correct pattern [OK]

Hint: Use LOOP with FETCH and EXIT WHEN NOT FOUND [OK]

Common Mistakes:

• Opening cursor before declaring
• Fetching before opening
• Using FETCH ALL which is invalid for cursors