PostgreSQL Tutorial
Data Types
Querying & Filtering Data
Managing Tables
Modifying Data
Conditionals
Control Flow
Transactions & Constraints
Working with JOINS & Schemas
Roles & Permissions
Working with Sets
Subquery & CTEs
User-defined Functions
Important In-Built Functions
PostgreSQL PL/pgSQL
Variables & Constants
Stored Procedures
Working with Triggers
Working with Views & Indexes
Errors & Exception Handling
In PostgreSQL, procedural code can be written using the PL/pgSQL language, which is a block-structured language and supports control flow mechanisms like loops, conditionals, and exceptions. Among the loops, the FOR loop is quite versatile.
There are primarily three types of FOR loops in PL/pgSQL:
FOR integer loop: Iterates over a range of integers.
FOR variable IN [ REVERSE ] start_integer .. end_integer
LOOP
-- commands
END LOOP;
Example:
DO $$
DECLARE
i integer;
BEGIN
FOR i IN 1..5 LOOP
RAISE NOTICE '%', i;
END LOOP;
END $$;
FOR record loop: Iterates over rows of a result set from a query.
FOR target_record IN query
LOOP
-- commands
END LOOP;
Example:
DO $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT column_name FROM information_schema.columns WHERE table_name = 'your_table_name' LOOP
RAISE NOTICE '%', rec.column_name;
END LOOP;
END $$;
FOR array loop: Iterates over elements of an array.
FOR variable IN [ REVERSE ] array_expression
LOOP
-- commands
END LOOP;
Example:
DO $$
DECLARE
element text;
arr text[] := ARRAY['a', 'b', 'c'];
BEGIN
FOR element IN ARRAY arr LOOP
RAISE NOTICE '%', element;
END LOOP;
END $$;
Each type of FOR loop has its use cases. The integer loop is good for when you have a fixed number of iterations. The record loop is invaluable when you're processing rows from a query, and the array loop lets you handle each element of an array.
In practice, remember that procedural code in PostgreSQL might not always be the most efficient way to handle data tasks. Wherever possible, try to use SQL's set-based operations, as they're typically more optimized in relational databases than row-by-row procedural operations.
How to use FOR loop in PostgreSQL:
FOR loop structure to iterate over a specified range or set of values.DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..5 LOOP
-- Your logic here
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
PostgreSQL FOR loop example:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..3 LOOP
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
Iterating over result sets with FOR loop in PostgreSQL:
DO $$
DECLARE
row_data record;
BEGIN
FOR row_data IN (SELECT column1, column2 FROM your_table) LOOP
RAISE NOTICE 'Column1: %, Column2: %', row_data.column1, row_data.column2;
END LOOP;
END $$;
Nested FOR loops in PostgreSQL:
DO $$
DECLARE
i INTEGER;
j INTEGER;
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..2 LOOP
RAISE NOTICE 'Iteration (%, %)', i, j;
END LOOP;
END LOOP;
END $$;
Dynamic SQL in FOR loop PostgreSQL:
DO $$
DECLARE
table_name VARCHAR := 'your_table';
column_name VARCHAR;
BEGIN
FOR column_name IN (SELECT column_name FROM information_schema.columns WHERE table_name = table_name) LOOP
RAISE NOTICE 'Column Name: %', column_name;
-- Your dynamic SQL logic here
END LOOP;
END $$;
FOR loop vs. WHILE loop in PostgreSQL:
DO $$
DECLARE
i INTEGER := 1;
BEGIN
FOR i IN 1..3 LOOP
RAISE NOTICE 'FOR Loop: Iteration %', i;
END LOOP;
i := 1;
WHILE i <= 3 LOOP
RAISE NOTICE 'WHILE Loop: Iteration %', i;
i := i + 1;
END LOOP;
END $$;
Using CONTINUE in FOR loop PostgreSQL:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..5 LOOP
IF i % 2 = 0 THEN
CONTINUE;
END IF;
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
BREAK statement in FOR loop PostgreSQL:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..5 LOOP
IF i = 3 THEN
EXIT;
END IF;
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
EXIT WHEN in PostgreSQL FOR loop:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..5 LOOP
EXIT WHEN i = 3;
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
FOR loop with cursor in PostgreSQL:
DO $$
DECLARE
row_data record;
my_cursor CURSOR FOR SELECT column1, column2 FROM your_table;
BEGIN
OPEN my_cursor;
FOR row_data IN my_cursor LOOP
RAISE NOTICE 'Column1: %, Column2: %', row_data.column1, row_data.column2;
END LOOP;
CLOSE my_cursor;
END $$;
Looping through an array with FOR loop in PostgreSQL:
DO $$
DECLARE
my_array INTEGER[] := ARRAY[1, 2, 3];
i INTEGER;
BEGIN
FOR i IN array_lower(my_array, 1)..array_upper(my_array, 1) LOOP
RAISE NOTICE 'Array Element: %', my_array[i];
END LOOP;
END $$;
Conditional FOR loop in PostgreSQL:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..5 LOOP
IF i % 2 = 0 THEN
RAISE NOTICE 'Iteration % (Even)', i;
ELSE
RAISE NOTICE 'Iteration % (Odd)', i;
END IF;
END LOOP;
END $$;
Using FOR loop for table updates in PostgreSQL:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..5 LOOP
UPDATE your_table SET column1 = i WHERE id = i;
RAISE NOTICE 'Row updated: ID %', i;
END LOOP;
END $$;
FOR loop with RANGE type in PostgreSQL:
DO $$
DECLARE
my_range INT4RANGE := '[1,5)';
i INTEGER;
BEGIN
FOR i IN my_range LOOP
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
Avoiding infinite loops in PostgreSQL FOR loop:
DO $$
DECLARE
i INTEGER := 1;
max_iterations INTEGER := 10;
BEGIN
FOR i IN 1..max_iterations LOOP
-- Your logic here
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
Dynamic iteration bounds in FOR loop PostgreSQL:
DO $$
DECLARE
start_index INTEGER := 2;
end_index INTEGER := 5;
i INTEGER;
BEGIN
FOR i IN start_index..end_index LOOP
RAISE NOTICE 'Iteration %', i;
END LOOP;
END $$;
Using FOR loop for batch processing in PostgreSQL:
DO $$
DECLARE
batch_size INTEGER := 100;
total_rows INTEGER;
i INTEGER;
BEGIN
SELECT COUNT(*) INTO total_rows FROM your_table;
FOR i IN 0 BY batch_size LOOP
-- Your batch processing logic here
RAISE NOTICE 'Processing rows % to %', i + 1, LEAST(i + batch_size, total_rows);
END LOOP;
END $$;