PostgreSQL FETCH

PostgreSQL FETCH Statement

The FETCH statement in PostgreSQL is used to retrieve rows from a result set returned by a query when working with cursors. Cursors allow you to iterate over a set of rows and retrieve them incrementally, which is useful when dealing with large datasets.

1. Basic Syntax for FETCH

FETCH [direction] [count] FROM cursor_name;

• direction: Determines the direction in which rows are fetched. Can be:
  • FORWARD (default): Fetches rows starting from the current position.
  • BACKWARD: Fetches rows in reverse.
• count: Number of rows to fetch (e.g., 5 to fetch five rows).
• cursor_name: The name of the cursor that was previously declared.

2. Example: Using FETCH with Cursors

Step 1: Declare a Cursor

First, you need to declare a cursor for a query.

BEGIN;  -- Start a transaction

DECLARE my_cursor CURSOR FOR
SELECT first_name, last_name FROM employees;

Here, my_cursor is a cursor for the employees table query.

Step 2: Fetch Rows Using the Cursor

Once the cursor is declared, you can use FETCH to retrieve rows.

Example 1: Fetch One Row

FETCH NEXT FROM my_cursor;

• Fetches the next row from the result set.

Example 2: Fetch Multiple Rows

FETCH 5 FROM my_cursor;

• Fetches the next 5 rows.

Example 3: Fetch Rows in Reverse Order

FETCH BACKWARD 3 FROM my_cursor;

• Fetches the previous 3 rows from the result set.

Step 3: Close the Cursor

After you finish fetching, you should close the cursor to release resources.

CLOSE my_cursor;
COMMIT;  -- End the transaction

3. Using FETCH in Stored Procedures

In stored procedures or functions, you can use FETCH to handle large datasets one row at a time. Here's an example of a stored procedure that fetches employees one by one:

CREATE OR REPLACE FUNCTION fetch_employees()
RETURNS VOID AS $$
DECLARE
    emp_cursor CURSOR FOR
    SELECT first_name, last_name FROM employees;
    emp_record RECORD;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Employee: %, %', emp_record.first_name, emp_record.last_name;
    END LOOP;
    
    CLOSE emp_cursor;
END;
$$ LANGUAGE plpgsql;

• This function fetches employees and prints their names one by one using RAISE NOTICE.

4. Summary of FETCH with Cursors

Operation	SQL Command
Declare a Cursor	DECLARE my_cursor CURSOR FOR SELECT ...
Fetch One Row	FETCH NEXT FROM my_cursor;
Fetch Multiple Rows	FETCH 5 FROM my_cursor;
Fetch Rows in Reverse	FETCH BACKWARD 3 FROM my_cursor;
Close Cursor	CLOSE my_cursor;

5. When to Use Cursors and FETCH

• Large Result Sets: Cursors and FETCH allow you to process a large number of rows without loading all the rows into memory at once.
• Row-by-Row Processing: When you need to process or manipulate rows one by one (e.g., calculations, updates).
• Iterative Queries: For operations that require iterative queries where you can fetch a subset of rows, perform operations, and then fetch more.

Would you like to see an example using a real table for iterating over data? 🚀