PostgreSQL/Postgre SQL/While
Simple While LOOP
<source lang="sql">
postgres=# postgres=# CREATE FUNCTION "count_by_two" (integer) RETURNS integer AS " postgres"# DECLARE postgres"# userNum ALIAS FOR $1; postgres"# i integer; postgres"# BEGIN postgres"# i := 1; postgres"# WHILE userNum < 20 LOOP postgres"# i = i+1; postgres"# return userNum; postgres"# END LOOP; postgres"# postgres"# END; postgres"# " LANGUAGE "plpgsql"; CREATE FUNCTION postgres=# postgres=# select count_by_two(12);
count_by_two
12
(1 row) postgres=#
</source>
Using the WHILE loop
<source lang="sql">
postgres=# postgres=# postgres=# -- Using the WHILE loop postgres=# postgres=# CREATE FUNCTION add_two_loop (integer, integer) RETURNS integer AS " postgres"# DECLARE postgres"# -- Declare aliases for function arguments. postgres"# low_number ALIAS FOR $1; postgres"# high_number ALIAS FOR $2; postgres"# postgres"# -- Declare a variable to hold the result. postgres"# result INTEGER = 0; postgres"# postgres"# BEGIN postgres"# -- Add one to the variable result until the value of result is postgres"# -- equal to high_number. postgres"# WHILE result != high_number LOOP postgres"# result := result + 1; postgres"# END LOOP; postgres"# postgres"# RETURN result; postgres"# END; postgres"# " LANGUAGE "plpgsql"; ERROR: function "add_two_loop" already exists with same argument types postgres=# postgres=# select add_two_loop(1,20);
add_two_loop
20
(1 row) postgres=#
</source>