Can You Do Loops in PostgreSQL?

Whenever we talk about database management systems, PostgreSQL always comes to mind. Data in PostgreSQL can be stored in many ways in the form of tables. To iterate through the stored data, we need a specific methodology that is looping. We use many looping statements and conditional statements to access data and apply different operations to it. This guide will help you in understanding the looping process in a PostgreSQL management system.

Looping Process in PostgreSQL

While doing operations, we often encounter such situations where we need to perform some methodologies many times repeatedly. This is a way of programming, and in the programming language, it is known as the looping process through the statements. So, when we need to perform a specific task several times we use loops in the code to loop the statements for numbers until our limit or requirement is achieved.


Install PostgreSQL database system on Windows 10. This database system can easily be installed by downloading the setup from the internet. After installation, configure it by applying basic changes. The system will ask you to add a password, and this password will be required every time you use PostgreSQL either psql or pgAdmin.

All those examples that are used here can be implemented on both PostgreSQL shell (psql) and the pgAdmin as well. To understand this looping concept on PostgreSQL, you need to have some knowledge of PostgreSQL data-related functions like SELECT, INSERT, DELETE commands.

Example 1

This example deals with the use of a FOR loop. This includes iterating through the integers using a FOR loop. The logic of applying the loop is the same as it is used in C, C++, or any other programming language, only the difference is in writing the syntax.

A variable that is an integer itself is firstly created and is accessible only inside the loop. As each iteration ends, the function of the loop is to add the step to the loop_cnt variable. Whereas in the case of the reverse option, the for loop subtracts steps from the integer variable created at the start, after every iteration has finished. The boundaries including the lower and upper bound, are specified by using from and to expressions. Before starting the loop, the for loop evaluates both these expressions.

Talking about the [by step] portion, the keyword followed by the step specifies the step of iteration with 1 by default.

Now, we will use an example here. The loop statement will iterate from 1 to 5 to display each number in every iteration. The looping process will start. Here, cnt is that integer variable with the help of which all the numbers are displayed. ‘cnt:%’ will display all the numbers.

After that, the loop will end. You can now take the output resultant values of the above-written code. You can see that the variable will display the variable with a new number in each line till the number 5 is attained.

Whereas if you want to get the series of numbers in the reverse order, you will add the keyword ‘reverse’ after the word ‘cnt’.

# for cnt reverse in 1..5 loop

Example 2

The second example deals with displaying student ids in descending order up to a specific limit. To proceed with this example, we must have some data present in PostgreSQL, so that we can operate on that. And as we know that the record is added in the form of tables. So, we will create a table named student, and this will be done by using a create command.

# CREATE TABLE student (student_id serial PRIMARY KEY, full_name VARCHAR(20), teacher_id INT);

You can see that the table student is created and has 3 attributes. Now, use an insert key to insert the data in the table.

# INSERT INTO student ( student_id, full_name, teacher_id ) VALUES  (1, 'Sharly', NULL), (2, 'John',1), (3, 'Jackson', 1), (4, 'S.lally', 1), (5, 'Pretty', 1), (6, 'Nickel', 2), (7, 'Mike ', 2), (8, 'Leonard', 2), (9, 'Doe', 2), (10, 'Zeen', 3);

Use a basic command in PostgreSQL to display the added record, SELECT statement. This will select the data from the table and will display it.

# select * from students;

Now we have added 10 rows in the table student. After inserting values, we will apply a query to display the information of the student with the student ids in descending order up to 4 points.

When we apply this query, you will see that the last 4 records that we have added are displayed as the limit for displaying is 4.

Example 3

Consider a simple example, here will display the multiples/table of a specific number to the 5th position. For this purpose, we will first create a function that will calculate and display the table of the number that will be passed through a function call.

This function will not return the value that’s why we have used void here. Initially, the value is declared as 1. To calculate the value, for loop will execute from 1 to 5. Every time the value of the counter is incremented. And that incremented value is multiplied by the number we have passed through the function call. The value will be displayed. Now, we will call the function by using a select statement here.

# select displayTable(5);

We want to display the table of 5, so we will pass 5 to the function. The result will contain multiples of 5. It means that initially, the counter was 1 so the value will be 5 because 5 will multiply with 5, after the increment, the value is 2, so the resultant value will be displayed as 10 and similarly so on.


This article is an answer to the topic under discussion, yes, we do loops in PostgreSQL. To iterate through data in PostgreSQL, you need to use loops in your code. Moreover, if you are interested in repeating any operation you want then you can also apply loops on them.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.