PL/pgSQL – FOR LOOP

In PostgreSQL’s PL/pgSQL, you can use the FOR loop statement to iterate over a sequence of values. FOR loops are useful when you want to perform a specific action a known number of times or iterate over a set of values, such as a range or an array. PL/pgSQL provides several types of FOR loops, including numeric loops and loops over query results.

Here’s an overview of the two primary types of FOR loops in PL/pgSQL:

Numeric FOR Loop:A numeric FOR loop allows you to iterate over a range of numbers. You specify the start and end values, and the loop iterates from the start value up to (and including) the end value.

FOR variable IN start_value..end_value 
LOOP 
-- Statements to execute in each iteration 
END LOOP; 

Here’s an example of a numeric FOR loop that prints numbers from 1 to 5:

CREATE OR REPLACE procedure print_numbers() 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
  FOR i IN 1..5 
LOOP 
  RAISE NOTICE 'Current value of i: %', i; 
END LOOP; 
END;  
$$; 

In this example, i iterates from 1 to 5, and we raise a notice with the current value of i in each iteration.

Cursor FOR Loop:A cursor FOR loop allows you to iterate over the result set of a query. You define a cursor that retrieves rows from a query, and then you use the FOR loop to process each row in the result set.

FOR record_variable IN cursor_name 
LOOP 
-- Statements to process each row in the result set 
END LOOP; 

Here’s an example of a cursor FOR loop that prints the names of employees from a hypothetical “employees” table:

CREATE OR REPLACE procedure print_employee_names()
LANGUAGE plpgsql 
AS $$ 
DECLARE 
   emp_record employees%ROWTYPE; 
BEGIN 
   FOR emp_record IN SELECT * FROM employees 
   LOOP 
   RAISE NOTICE 'Employee name: %', emp_record.name; 
   END LOOP; 
END; 
$$ ;

In this example, we define a cursor named emp_record that retrieves rows from the “employees” table. The FOR loop iterates over the result set and raises a notice with the name of each employee.

You can call these procedures to observe the FOR loops in action:

CALL print_numbers(); 
-- Prints numbers 1 to 5 
CALL print_employee_names(); 
-- Prints employee names from the table

PL/pgSQL FOR loops are versatile and allow you to iterate over a wide range of values or query results, making them a valuable tool for various programming tasks.