How to Use Loop in PostgreSQL

Shihab Sikder Feb 02, 2024
  1. Use the for Loop Statement in PostgreSQL
  2. Use the WHILE Loop Statement in PostgreSQL
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.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website