Iterating over a result set is a very crucial task in PL/pgSQL that can be achieved using loops. Loops are an essential part of programming that allows us to execute a block of code repeatedly. Among developers, the most popularly used loop is the “for” loop which is used for executing a block of code continually. Postgres allows us to utilize the for loop to loop through a query’s result set.

This blow demonstrates how to use a for loop to iterate over a result set of a Postgres query.

PL/pgSQL: Iterating Over a Query’s Result Set Using For Loop

Use the provided syntax to iterate over the query’s result set:

[label]
for count IN query 
LOOP
   statements
END LOOP[ label ];

In the above query the “count” represents a RECORD-TYPE variable that will keep the rows of a result set.

Consider the following examples for a profound understanding of the PL/pgSQL for-loop.

Example: Iterating Over a Query’s Result Set

In this example, we will utilize the “product_details” table whose content is enlisted in the following snippet:

Now we will utilize the for loop to fetch the product names of the top 3 cheapest products:

DO
$$
DECLARE rec RECORD;
BEGIN
FOR rec IN SELECT pro_name, pro_price 
FROM product_details 
ORDER BY pro_price, pro_name
LIMIT 3
LOOP 
RAISE NOTICE '% %$', rec.pro_name, rec.pro_price;
   END LOOP;
END;
$$

In the above snippet:

• A record type variable named “rec” is declared that will keep/hold the rows of a result set returned by the SELECT query.
• The table’s data is sorted according to the “pro_price” and “pro_name” columns.
• The LIMIT clause makes sure to get only three records from the result set.
• The RAISE NOTICE is used with the loop to iterate over the result set and print the respective results.

The output shows that the for loop has successfully iterated over the result set.

Conclusion

Postgres allows us to utilize the for loop to loop through a query’s result set. A record-type variable can be declared to keep/hold the rows of a result set returned by the SELECT query. The RAISE NOTICE can be used within the loop to iterate over the result set and print the desired results. This post has illustrated the detailed procedure to iterate over a result set in Postgres.