{"id":1554,"date":"2025-01-08T12:01:25","date_gmt":"2025-01-08T05:01:25","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1554"},"modified":"2025-01-08T13:23:20","modified_gmt":"2025-01-08T06:23:20","slug":"plpgsql-while","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-while\/","title":{"rendered":"PL\/pgSQL WHILE Loop Statement"},"content":{"rendered":"\n

Summary:<\/strong> In this tutorial, you’ll learn how to execute a code block if a condition is true using the PL\/pgSQL WHILE<\/code> statement.<\/p>\n\n\n\n

Overview of PL\/pgSQL WHILE loop statement #<\/a><\/h2>\n\n\n\n

The WHILE<\/code> statement allows you to execute a code block repeatedly if a condition is true.<\/p>\n\n\n\n

Here’s the syntax of the WHILE<\/code> statement:<\/p>\n\n\n

[<<label>><\/span>]\nWHILE<\/span> condition LOOP<\/span>\n    statements;\nEND<\/span> LOOP<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The WHILE<\/code> evaluates the condition<\/code>. If the condition<\/code> is true, the WHILE<\/code> executes the statements<\/code>.<\/p>\n\n\n\n
It’ll re-evaluate the condition<\/code> before each iteration and execute statements<\/code> as long as it is true.<\/p>\n\n\n\n
Since the WHILE<\/code> statement evaluates the condition<\/code> before each iteration, it is known as a pretest loop<\/em>.<\/p>\n\n\n\n
To avoid an indefinite loop, you may need to change variables to make the condition<\/code> not true at some point.<\/p>\n\n\n\n
Basic PL\/pgSQL WHILE statement example #<\/a><\/h2>\n\n\n\n
The following example defines a function that calculates the sum of a sequence of integers from 1 to n<\/code>:<\/p>\n\n\n
CREATE<\/span> OR REPLACE<\/span> FUNCTION<\/span> total(n INT<\/span>)\nRETURNS<\/span> INT<\/span>\nAS<\/span>\n$$\nDECLARE<\/span>\n    v_current INT<\/span> = 1<\/span>;\n    v_result INT<\/span>  = 0<\/span>;\nBEGIN<\/span>\n    WHILE<\/span> v_current <= n LOOP<\/span>\n        v_result = v_result + v_current;\n        v_current = v_current + 1<\/span>;\n    END<\/span> LOOP<\/span>;\n    RETURN<\/span> v_result;\nEND<\/span>;\n$$<\/span>\nLANGUAGE<\/span> plpgsql;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How the function works:<\/p>\n\n\n\n
First, declare the variables and initialize their values to one and zero: <\/p>\n\n\n
v_current INT<\/span> = 1<\/span>;\nv_result INT<\/span>  = 0<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
We’ll use v_current<\/code> to iterate from 1 to n<\/code> and v_result<\/code> to store the sum.<\/p>\n\n\n\n
Second, add the value of the v_current<\/code> to the v_result<\/code> and increment v_current<\/code> by one as long as the v_current<\/code> is less than or equal to n<\/code>: <\/p>\n\n\n
WHILE<\/span> v_current <= n LOOP<\/span>\n   v_result = v_result + v_current;\n   v_current = v_current + 1<\/span>;\nEND<\/span> LOOP<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The loop effectively calculates the sum of all integers from 1 to n<\/code>.<\/p>\n\n\n\n
Third, return the total after the loop completes:<\/p>\n\n\n
RETURN<\/span> v_result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement calls the total()<\/code> function to calculate the sum of a sequence from 1 to 10:<\/p>\n\n\n
SELECT<\/span> total(10<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 total\n-------<\/span>\n    55<\/span>\n(1<\/span> row<\/span>)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n