How to Use Loop in PostgreSQL
This article will discuss and demonstrate how to use loops in PostgreSQL.
Use the for Loop Statement in PostgreSQL
The for loop has some defined properties. It would be best to assign a variable that will increment or decrement in every loop.
Then, it would help if you gave a range for iteration. Here is a basic structure of the FOR statement in Postgres.
do $$
begin
for i in 1..10 loop
-- some sql query
end loop;
end; $$
Here, the loop will run for 10 iterations. In the SQL query, we can use the value of i.
Now, let’s demonstrate an example.
postgres=# \d accounts;
Table "public.accounts"
Column | Type | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+-------------------------------------------
user_id | integer | | not null | nextval('accounts_user_id_seq'::regclass)
username | character varying(50) | | not null |
password | character varying(50) | | not null |
email | character varying(255) | | not null |
contact | character varying(20) | | |
postcode | integer | | |
age | integer | | |
height | integer | | |
timestamp | timestamp without time zone | | | CURRENT_TIMESTAMP
You can see the table description using the \d command before the table name. Now, we want to know the username of the top 10 youngest users.
So, the SQL will be,
do $$
declare
_record record;
begin
for _record in SELECT username, age
FROM accounts
ORDER BY age asc
LIMIT 10
LOOP
RAISE NOTICE 'Username: % (% years)', _record.username, _record.age;
END LOOP;
END; $$;
Output:
NOTICE: Username: ChDCfhl (13 years)
NOTICE: Username: VmgqJMB (13 years)
NOTICE: Username: MbOTFXt (13 years)
NOTICE: Username: WUKNQYe (13 years)
NOTICE: Username: ldWoKpz (13 years)
NOTICE: Username: uksgPZS (13 years)
NOTICE: Username: YXuaLda (14 years)
NOTICE: Username: PXrxKvO (14 years)
NOTICE: Username: hJQXFHO (14 years)
NOTICE: Username: XxwNIOR (14 years)
Here, _record is a special type of PSQL variable that stores the results or the return data from the SQL query. We can also populate a table with a FOR statement.
Use the WHILE Loop Statement in PostgreSQL
In the FOR loop, you need to define the range for the variable or the iteration. On the other hand, the WHILE loop doesn’t have any range.
It will run until it meets its conditions. The basic structure of the WHILE statement is given below.
DO $$
DECLARE
-- declare variable if you need
BEGIN
WHILE condition LOOP
-- SQL QUERY / RASIE
-- Increment or decrement variable
-- Otherwise, it may fall into an infinite loop
END LOOP;
END$$;
Most of the time, you need variables to modify the value inside the condition. Suppose, in the condition you have, id>10; the loop will run until the id>10, but we are not changing the ID in the script.
If in the first iteration, the ID is greater than 10 and we don’t modify it during the iteration, the SQL will run infinitely as the condition is always true.
There are several SQL commands we can use. The first command will create a table; the second PL/SQL will insert data into the table using a WHILE loop.
After successfully inserting the data, we show all the data in the table.
CREATE TABLE randoms(
ID int primary key,
Random int
);
do $$
declare
id INTEGER :=1;
begin
WHILE id < 10 LOOP
INSERT INTO randoms(ID, Random) VALUES(id, random());
id := id+1;
END LOOP;
END; $$;
SELECT * FROM randoms;
Output:
postgres=# CREATE TABLE randoms(
postgres(# ID int primary key,
postgres(# Random int
postgres(# );
CREATE TABLE
postgres=#
postgres=# do $$
postgres$# declare
postgres$# id INTEGER :=1;
postgres$# begin
postgres$# WHILE id < 10 LOOP
postgres$# INSERT INTO randoms(ID, Random) VALUES(id, random());
postgres$# id := id+1;
postgres$# END LOOP;
postgres$# END; $$;
DO
postgres=#
postgres=# SELECT * FROM randoms;
id | random
----+--------
1 | 0
2 | 1
3 | 1
4 | 0
5 | 0
6 | 1
7 | 0
8 | 1
9 | 0
(9 rows)
postgres=#
To learn more about Postgres Loops, you can visit the official documentation from here. Also, if you want to learn more queries with FOR and WHILE loops, you can read this blog.
