LOOP 循环

teledb=# CREATE OR REPLACE PROCEDURE p_loop()  AS
$$  
DECLARE
     v_id INTEGER := 1;
BEGIN     
    LOOP   
        RAISE NOTICE '%',v_id;
        EXIT WHEN random()>0.8;
        v_id := v_id + 1;
    END LOOP ;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE

teledb=# CALL p_loop();
NOTICE:  1
NOTICE:  2
NOTICE:  3
CALL
teledb=#

WHILE 循环

teledb=# CREATE OR REPLACE PROCEDURE p_while()  AS
$$  
DECLARE
    v_id INTEGER := 1;
    v_random float8 ;
BEGIN     
    LOOP   
       RAISE NOTICE '%',v_id;
       v_id := v_id + 1;
       v_random := random();
       IF v_random > 0.8 THEN
           RETURN;
       END IF;
    END LOOP ;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_while();
NOTICE:  1
CALL

FOR 循环

teledb=# CREATE OR REPLACE PROCEDURE p_for()  AS
$$  
BEGIN     
    FOR i IN 1..3 LOOP
        RAISE NOTICE 'i = %',i;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for();
NOTICE:  i = 1
NOTICE:  i = 2
NOTICE:  i = 3
CALL

teledb=# CREATE OR REPLACE PROCEDURE p_for_reverse()  AS
$$  
BEGIN     
    FOR i IN REVERSE 3..1 LOOP
        RAISE NOTICE 'i = %',i;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for_reverse();
NOTICE:  i = 3
NOTICE:  i = 2
NOTICE:  i = 1
CALL

使用REVERSE 递减。

teledb=# CREATE OR REPLACE PROCEDURE p_for_by()  AS
$$  
BEGIN     
    FOR i IN 1..8 BY 2 LOOP
        RAISE NOTICE 'i = %',i;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for_by();
NOTICE:  i = 1
NOTICE:  i = 3
NOTICE:  i = 5
NOTICE:  i = 7
CALL
teledb=#

使用BY 设置步长。

FOR 循环查询结果

teledb=# CREATE OR REPLACE PROCEDURE p_for_record()  AS
$$  
DECLARE
    v_rec RECORD;
BEGIN       
    FOR v_rec IN SELECT relname,relkind FROM pg_class limit 2 LOOP
        RAISE NOTICE '%',v_rec;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for_record();
NOTICE:  (pg_stat_statements,v)
NOTICE:  (pg_proc,v)
CALL
teledb=#

FOREACH 循环一个数组

teledb=# CREATE OR REPLACE PROCEDURE p_foreach()  AS
$$  
DECLARE
    v_random_arr float8[]:=ARRAY[random(),random()];
    v_random float8;
BEGIN       
    FOREACH v_random IN ARRAY v_random_arr LOOP
        RAISE NOTICE '%',v_random ;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_foreach();
NOTICE:  0.744417542591691
NOTICE:  0.804096563253552
CALL
teledb=#

teledb=# CREATE OR REPLACE PROCEDURE p_foreach_slice()  AS
$$  
DECLARE
    v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];
    v_random float8;
BEGIN       
    FOREACH v_random SLICE 0 IN ARRAY v_random_arr LOOP
        RAISE NOTICE '%',v_random ;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_foreach_slice();
NOTICE:  0.0220407997258008
NOTICE:  0.898449067492038
NOTICE:  0.190678883343935
NOTICE:  0.103653562255204
CALL
teledb=#

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

teledb=# CREATE OR REPLACE PROCEDURE p_foreach_slice_1()  AS
$$  
DECLARE
    v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];
    v_random float8[];
BEGIN       
    FOREACH v_random SLICE 1 IN ARRAY v_random_arr LOOP
        RAISE NOTICE '%',v_random ;
    END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_foreach_slice_1();
NOTICE:  {0.248282201588154,0.757913041394204}
NOTICE:  {0.0194511725567281,0.43799454299733}
CALL

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