Postgres Loop Types

Introduction

This lesson is a study of the various Postgres loop types available for use in database programming. The loop types we will learn about here include Loop, For Loop, and While Loop. We will also include some SQL examples of loops where we use the PostgreSQL array as part of queries.

Prerequisites

  • Understanding of what “loop” means: Repeating some code for a specified number of times and/or until some requirement is met. This looping process often involves incrementing a variable in order to track a number or otherwise limit the scope of the number of iterations in the loop.
  • Experience with basic Postgres SQL statements such as SELECT, FROM, UPDATE, and INSERT.
  • Basic understanding of arrays. No worries! We’ll begin the lesson by sharing a basic instruction on arrays in PostgreSQL.

Postgres Array

An array is a group or list of items. Arrays can increase efficiency and save work and time. “1, 9, 3, 4, 5, 2” is an array of integers. “Tim, Jed, Sal, Terry, James” is an array of text items. We use “items” to describe each individual in the array.

Postgres has an “Array” data type. Within a “cell” – cross section between a database row and column – you can have array data types, as opposed to text or integer data types.

Postgres Array example

CREATE TABLE tbl_products (
    t_name_product text
    , arr_i_security_level INTEGER[]
);

Analysis

In the SQL above, the text data type used for “t_name_product” may be familiar. The column we named “arr_i_security_level” may be new to you as it is an Array data type. The brackets (“[]”) are how we direct PostgreSQL to “set this column as an array type.”

To start off our lesson on PostgreSQL loop types, we will first look at the most simple of loop types available in Postgres, which requires no conditions.

The PostgreSQL Loop

The Postgres Loop with no conditions relies on an EXIT WHEN to exit the loop. Here’s how it looks:

Postgres LOOP syntax

LOOP
    [statements];
    EXIT WHEN [condition met]
END LOOP;

Analysis

Keep in mind the above Postgres loop will run forever if the [conditions met] clause does not evaluate to positive. This is where the EXIT WHEN clause comes in handy. See the PostgreSQL loop example below.

Postgres LOOP example

i_counter := 0
LOOP
    RAISE NOTICE i_counter;
    i_counter := i_counter + 1;
    EXIT WHEN i_counter = 4;
END LOOP;

GeSHi Error: GeSHi could not find the language output (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

The PostgreSQL For loop

For loops can be used to loop through a range of integers or objects. We’ll show examples of how to do both looping through numbers and looping through other types. The Postgres For Loop may have the most variations in all the loop types in PostgreSQL.

Postgres FOR loop syntax

FOR [counter name] IN [REVERSE] [START VALUE] .. [END VALUE] [BY stepping]
LOOP
    [statements];
END LOOP;

Postgres FOR loop example

BEGIN
    FOR i_num_count IN 100 .. 400 BY 100
    LOOP
        RAISE NOTICE 'Number Count: %', i_num_count;
    END LOOP;
END;

GeSHi Error: GeSHi could not find the language output (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Analysis

  • i_num_count: A variable used to track and store the current position we are at in the for loop.
  • 100 .. 400: The range (start and end value) of our loop.
  • BY 100: The “stepping”; how many values to skip at each iteration through the loop.
  • LOOP: The beginning of our For loop.
  • END LOOP: The end of our For loop.

Another powerful way to use a For loop in Postgres is to iterate through an array of objects, where you don’t need to know how many objects are in that array.

For Loop through array example

BEGIN
    t_names := string_to_array("Sally, Molly, Timmy", ",")
    FOR t_name IN t_names
    LOOP
        RAISE NOTICE 'Name: %', t_name;
    END LOOP;
END;

GeSHi Error: GeSHi could not find the language output (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

For loop through query results

Finally, we can use the For loop to iterate through the results of a query!

BEGIN
    FOR my_record IN SELECT t_name FROM tbl_users
    LOOP
        RAISE NOTICE 'Name: %', my_record;
    END LOOP;
END;

GeSHi Error: GeSHi could not find the language output (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

The PostgreSQL While loop

In Postgres, the WHILE loop is similar to the For loop we discussed above. It requires a condition and statements to execute with each iteration of the While loop.

Postgres While loop syntax

WHILE [conditions]
LOOP
    [statements TO EXECUTE]
END LOOP;

Use WHILE loop in SQL

It’s important to consider that When writing SQL, a HAVING or WHERE clause will usually suffice for limiting which rows are retrieved. Sometimes, a WHILE LOOP is more efficient, while in other situations it adds complexity to your SQL. So please ask yourself, “Could I do this with a WHERE or HAVING statement instead?” In the situations where a HAVING or WHERE clause won’t give you the results you want, WHILE may be what you need. Let’s look at some use cases.

Number counting with WHILE loop

-- (1) Declaration of vars.
DECLARE
i_increment INT := 1;
i_num_current INT := 1;
i_finish INT := 5;
-- (2) Tell Postgres the scope of the application, in a BEGIN to END nest.
BEGIN
    -- (3) Define our iterations within a LOOP to END LOOP nest.
    WHILE i_num_current <= i_finish
    LOOP
        -- (4) Code to be used. Here we are doing two things:
        -- (a) Increment i_num_current var.
        i_num_current := i_num_current + i_increment;
        -- (b) Output i_num_current value.
        RAISE NOTICE i_num_current;
        -- FYI: In most other forms of TSQL, we use "Print" instead of "Raise Notice".
    END LOOP;
END;

Analysis

  • DECLARE the variables we’ll use in the Postgres WHILE loop.
  • BEGIN delineates the start of our application.
  • Set the PostgreSQL loop type as WHILE, along with a condition, which is for i_num_curren to stay smaller than or equal to i_finish.

Executing the above code will give the following results:

1
2
3
4
5

Now that you understand the basics of using a WHILE loop in Postgres, let’s modify a database!

Fill table with consecutive numbers

Now to apply our new understanding of the Postgres WHILE loop to a database table. We start by using a simple case of filling a column with integers.

To start, let’s set up a simple test table with SQL’s CREATE TABLE command:

CREATE TABLE public.test (
    i_numberoo int8
);

When we execute the code above in PostgreSQL, we get a new, empty table with one column. That’s all we need for this example. Now to script a Postgres query to fill 300 rows with an integer we’ll increment using a WHILE LOOP from PostgreSQL.

DECLARE
i_increment INT := 1;
i_num_current INT := 1;
i_finish INT := 300;
BEGIN
    WHILE i_num_current <= i_finish
    LOOP
        i_num_current := i_num_current + i_increment;
        INSERT INTO tbl_number_test (i_numberoo)
        SELECT i_num_current;
    END LOOP;
END;

In the SQL above, the primary change we made was changing “i_finish” to 300, replaced “RAISE NOTICE i_num_current” with “INSERT INTO test (i_numberoo) SELECT i_num_current”. This adds records to the “tbl_number_test” table. In other words, we filled the “i_numberoo” field with the numbers 1 through 300, one per row.

Loop query results

To make sure we are getting what we want, after we execute that query, we can check to see how many records were created:

SELECT COUNT(*) FROM tbl_number_test;

When we execute the above SELECT command, PostgreSQL will return a count of 300 because we inserted 300 rows into the “tbl_number_test” table.

Conclusion

In this lesson we explored use of the various Postgres loop types available via SQL commands. We started with the most basic, LOOP, which has no conditions and thus requires an EXIT. We moved on to the FOR loop, and finally took the most in-depth look at the PostgreSQL WHILE loop. For each of these, we looked at the loop syntax and at least one loop example.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.