LOOP 循环

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#      v_id INTEGER := 1;
teledb$# BEGIN         
teledb$#     LOOP     
teledb$#         RAISE NOTICE '%',v_id;
teledb$#         EXIT WHEN random()>0.8;
teledb$#         v_id := v_id + 1;
teledb$#     END LOOP ;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  1
NOTICE:  2
 f27 
-----
(1 row)

使用EXIT退出循环。

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_id INTEGER := 1;
teledb$#     v_random float8 ;
teledb$# BEGIN         
teledb$#     LOOP     
teledb$#        RAISE NOTICE '%',v_id;
teledb$#        v_id := v_id + 1;
teledb$#        v_random := random();
teledb$#        IF v_random > 0.8 THEN
teledb$#            RETURN;
teledb$#        END IF;
teledb$#     END LOOP ;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  5
 f27 
-----
(1 row)
teledb=# 

使用RETURN退出循环返回。

WHILE循环

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_id INTEGER := 1;
teledb$#     v_random float8 := random() ;
teledb$# BEGIN         
teledb$#     WHILE v_random > 0.8 LOOP
teledb$#         RAISE NOTICE '%',v_id;
teledb$#         v_id := v_id + 1;
teledb$#         v_random = random();
teledb$#     END LOOP;  
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  1
 f27 
-----
(1 row)

FOR循环

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# BEGIN         
teledb$#     FOR i IN 1..3 LOOP
teledb$#         RAISE NOTICE 'i = %',i;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  i = 1
NOTICE:  i = 2
NOTICE:  i = 3
 f27 
-----
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# BEGIN         
teledb$#     FOR i IN REVERSE 3..1 LOOP
teledb$#         RAISE NOTICE 'i = %',i;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  i = 3
NOTICE:  i = 2
NOTICE:  i = 1
 f27 
-----
(1 row)

使用REVERSE递减。

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# BEGIN         
teledb$#     FOR i IN 1..8 BY 2 LOOP
teledb$#         RAISE NOTICE 'i = %',i;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27(); 
NOTICE:  i = 1
NOTICE:  i = 3
NOTICE:  i = 5
NOTICE:  i = 7
 f27 
-----
(1 row)

使用BY设置步长。

FOR循环查询结果

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_rec RECORD;
teledb$# BEGIN         
teledb$#     FOR v_rec IN SELECT * FROM public.t LOOP
teledb$#         RAISE NOTICE '%',v_rec;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  (1,teledb_pg)
NOTICE:  (2,pgxz)
 f27 
-----
(1 row)

FOREACH循环一个数组

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_random_arr float8[]:=ARRAY[random(),random()];
teledb$#     v_random float8;
teledb$# BEGIN         
teledb$#     FOREACH v_random IN ARRAY v_random_arr LOOP
teledb$#         RAISE NOTICE '%',v_random ;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  0.452758576720953
NOTICE:  0.975814974401146
 f27 
-----
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];
teledb$#     v_random float8;
teledb$# BEGIN         
teledb$#     FOREACH v_random SLICE 0 IN ARRAY v_random_arr LOOP
teledb$#         RAISE NOTICE '%',v_random ;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  0.0588191924616694
NOTICE:  0.368828620761633
NOTICE:  0.813376842066646
NOTICE:  0.415377039927989
 f27 
-----
(1 row)

循环会通过计算expression得到的数组的个体元素进行迭代。

teledb=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];
teledb$#     v_random float8[];
teledb$# BEGIN         
teledb$#     FOREACH v_random SLICE 1 IN ARRAY v_random_arr LOOP
teledb$#         RAISE NOTICE '%',v_random ;
teledb$#     END LOOP;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27();
NOTICE:  {0.578366641886532,0.78098024148494}
NOTICE:  {0.783956411294639,0.450278480071574}
 f27 
-----
(1 row)

通过一个正SLICE值，FOREACH通过数组的切片而不是单一元素迭代。