Repeat Function in Postgres SQL

Introduction

In this tutorial to use the Repeat function to achieve various text- and integer- related tasks in Postgres SQL, including:

• What? What’s the syntax of the REPEAT function and what does the function do? How do we use the Repeat() function in our PostgreSQL queries? What parameters does it use?
• Why? When do we need this function in our queries?
• Extras We’ll get exposure to various SQL commands including SELECT, FROM, and WHERE. We’ll work some with the ARRAY variable type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and how to cast an integer as text.

Prerequisites

• Knowledge of how to write SQL in Postgres (or MS SQL Server, MySQL, Oracle, etc.), using PostgreSQL’s free database admin tool or another relational database administration tool, or with code or script languages like PHP, Python, Javascript, Java, C#, ASP.Net, VB.Net, Note.js, Ruby, etc.) that allows a connection to your database as well as a method for sending SQL commands or requests to query or make changes to a database.
• Knowledge of the use of basic SQL statements, including SELECT, FROM, and WHERE.
• Understanding of what the following terms mean: text types, strings, and integers.
• Optional: Tutorial on naming conventions that explains why we prefix our variables, column names, table names, etc. (all objects, really) as you see us doing here. For example, naming “tphrase_original” with the “t” you see at the beginning in order to delineate it as a “text” object.

What does the REPEAT function do in Postgres SQL?

We use PostgreSQL’s REPEAT function to duplicate a string a specific number of times. As we’ll see in an example below, sometimes we want to pad text or numbers with zeros in order to obtain visual or other kinds of uniformity.

How to use the Repeat function with SQL and parameters

Syntax of the REPEAT() function

We give the REPEAT function two parameters:

• t_what_to_repeat: The string we feed to the function.
• i_repetitions: Tells REPEAT() how many characters we want the resulting string to be in length.

Let’s use the syntax above and take a step in the direction of more complexity and more understanding. The mini-project is to take four words and “pad” all of them on their left so that they are all the same length, appearing as if they are right justified.

Analysis of the above code

• DECLARE: Here we (1) Initialize and fill an array with data, holding our four words. We name the array “a_t_technologies”. (2) We know the longest word in our array is “Javascript”, which is 10 characters long. We want to pad all words in our array so they end up being 10 characters long, which is why we are using the space character (” “) for padding. This is also why we assigned “10” to “i_result_length”. (3) As mentioned above for “t_what_to_repeat”, we chose a space so spaces are REPEATed at the beginning of each of the words in our array until that word reaches a total of 10 in LENGTH. (4) We are initializing “i_current_array_spot” to be an integer of zero to get our loop started. This variable will be used to count through the WHILE loop in this routine, pick the word in our array we are currently working on, and end the loop. (5) “i_size_of_array” is used in our loop below to tell PostgreSQL when to exit the loop.
• WHILE: Here we are setting up our loop to cycle from 0 to 3. You will see below how we add 1 to the value just before accessing which word in the array we want to modify.
• LOOP: On this line we increase “i_current_array_spot” by 1, so that the actual array item being pulled is one higher than 0 to 3; becoming 1 to 4.
• RAISE NOTICE: Here we return “t_result” to see the following output.

See now how each word was “padded” to a maximum of 10 characters, pushing every word to have a uniform right margin?

NOTE: The LPAD() function is another way to accomplish this same task; usually with more efficiency.

Let’s explore another potential usage of the REPEAT() function in PostgreSQL. The scenario: A data table with integers ranging in the number of decimal places and we need to pad these numbers with zeros so they have a uniform appearance.

Let’s begin with the following Postgres table named “technologies”.

Analysis

• SELECT t_name_tech: For reference, we are displaying columns named “t_name_tech” and “t_category_tech”.
• i_rating::text AS t_word: Here we are recasting “i_rating”, an integer, to be a string (text object) called “t_word” so we can later use the LENGTH and REPEAT functions on it.
• LENGTH(t_word) AS i_word_length: We need to know the current length of our word, as you will see on the next line. Note: We could calculate that on the fly, within the formula below, but we’re doing it explicitly here to increase readability and ease of understanding.
• REPEAT(t_what_to_repeat, i_result_length - i_word_length) || t_word AS t_rating_padded: Seed the REPEAT() function with two parameters: “twhat_to_repeat”, which is a zero, and i_word_length – i_result_length, which tells Postgres, “Take the length of our word and tell us how close to 10 characters it is. If our word is 8 characters long, for example, we know 2 zeros need to be added in order to get to 10. _Then we use the concatenation symbol of “||” to add our “t_word” to the right side of those zeros and name the whole resulting string AS “t_rating_padded”. You might ask, “Why not recast “t_rating_padded” back to an integer?” That’s because we would lose the zeros we just added if we went that route.
• FROM: tells the Postgres database engine which table to use as the source for our SQL data.

The following results are returned when we execute the SQL:

Conclusion

In this tutorial we learned how to use the REPEAT() function in our Postgres SQL, including working through two detailed real world examples. We also practiced the use of various PostgreSQL query commands including SELECT, FROM, and WHERE. We worked with an ARRAY type, the ARRAY_LENGTH function, the WHILE loop construct, the LENGTH function, the RAISE NOTICE command, and how to “cast” an integer into a text string using the “::” symbol.