PL/pgSQL – Cursors

PL/pgSQL supports the use of cursors to retrieve and manipulate result sets from SQL queries in PostgreSQL database. Cursors are particularly useful when you need to work with large result sets or when you want to process rows one by one within a PL/pgSQL function or procedure.

Here’s a basic overview of cursors in PL/pgSQL:

Declaring a Cursor:You declare a cursor using the DECLARE statement, specifying the SQL query that defines the result set for the cursor.

DECLARE cursor_name CURSOR [FOR] SELECT query; 

For example:

DECLARE emp_cursor CURSOR FOR SELECT * FROM employees;

Opening a Cursor:To start fetching rows from the cursor, you need to open it using the OPEN statement.

OPEN cursor_name; 

For example:

OPEN emp_cursor;

Fetching Rows:You can use the FETCH statement to retrieve rows one by one from the cursor. Common fetch options include INTO to store the fetched values into variables.

FETCH [NEXT] FROM cursor_name INTO variable1, variable2, ...; 

For example:

FETCH NEXT FROM emp_cursor INTO emp_id, emp_name;

Looping Through Rows:Cursors are often used within loops to process each row in the result set. A common approach is to use a WHILE loop to iterate through the cursor until no more rows are available.

WHILE condition 
LOOP 
  -- Fetch rows and perform operations 
END LOOP;

Closing the Cursor:After you’ve finished working with the cursor, it’s essential to close it using the CLOSE statement to release database resources.

CLOSE cursor_name; 

For example:

CLOSE emp_cursor;

Here’s a complete example of a PL/pgSQL function that uses a cursor to fetch and process rows from an “employees” table:

CREATE OR REPLACE FUNCTION process_employees() 
RETURNS VOID 
AS $$ 
DECLARE 
     -- Declare a cursor
     emp_cursor CURSOR FOR SELECT * FROM employees; 
     emp_record employees%ROWTYPE; 
BEGIN 
  -- Open the cursor 
  OPEN emp_cursor; 
     -- Loop through the cursor and process each row 
     LOOP 
       FETCH NEXT FROM emp_cursor INTO emp_record; 
       EXIT WHEN NOT FOUND; 
       -- Perform operations on emp_record (e.g., print values) 
       RAISE NOTICE 'Employee ID: %, Name: %', emp_record.id, emp_record.name; 
     END LOOP; 
  -- Close the cursor 
  CLOSE emp_cursor; 
END; 
$$ LANGUAGE plpgsql;

You can execute the function process_employees() to process all the employees in the “employees” table one by one. Cursors provide flexibility when working with result sets in PL/pgSQL and can be used for various data processing tasks.