Each iteration will add the total revenue from each customer and store it in the cust_id variable. The “total_revenue” variable will store the values of the “order_total” value of the current customer. It creates the “cust_id” variable running in the loop storing values from the “customer_id” from the orders table. RAISE NOTICE 'Customer % total revenue: %', cust_id, total_revenue SELECT SUM(order_total) INTO total_revenue FROM orders WHERE customer_id = cust_id Use the following code to fetch data from the table using For loop: DO $$įOR cust_id IN SELECT DISTINCT customer_id FROM orders LOOP This example applies the For loop on the PostgreSQL table to display the data of the orders table: SELECT * FROM orders The above query uses the by keyword to specify the step size: The above code will simply print the numbers in the reverse order starting from 10 to 1:Įxample 3: Customizing Steps Using For LoopĮxecute the following code to print numbers of your own choice by specifying the customized steps: do $$ This example uses the reverse keyword to execute the for loop backward: do $$ The raise notice clause is used to print the iterations of the for loop on the screen:Įxample 2: Reverse Iteration Using For Loop Running the above query will simply print numbers for each iteration which starts from 1 and ends at 10. Use the following query in PostgreSQL to print the first 10 integers on the screen: do $$ After that, the statement or the body of the loops is written which determines what to fetch from the table or display as the result. In the above snippet, replace the “from” and “to” with the starting and ending range of the loop, respectively. For loop usually adds the steps to the variable but the reverse keyword is used to run the loop backward by subtracting steps from the variable. For keyword will start the loop followed by the name of the integer variable which will only be executable in the loop. The following is the syntax of the for loop in PostgreSQL: įor in from. The loop will keep working until the condition is satisfied and it will immediately stop when the condition becomes false. To use the for loop, the user is required to provide the starting point and then also enter the ending point of the loop called condition. This guide will explain how to use a for loop in PostgreSQL.įor loop is the most used loop often called a counter loop meaning that it will act as the counter by going through the same steps multiple times. To avoid these complexities, PostgreSQL allows working with loops to apply the same query on each row and column of the selected table. Applying conditions on each data individually is also very complicated and frustrating as the user has to write the same query multiple times. Traversing through the PostgreSQL database to fetch data asked by the user through query can take a huge amount of time.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |