PG数组类型使用

数组遍历

create or replace function func_test()
returns integer
language 'plpgsql'
as
$$
declare 
    rec record;
    # 定义数组并初始化
    tmp_res varchar ARRAY:=ARRAY[''];
begin
    # 2.tbl_ua 只针对平级域
        for rec in select id from test where code = 3
    loop
        
        tmp_res=array_append(tmp_res,rec.id::varchar);

    end loop;
    #    从第二个元素开始遍历
    for i in 2..array_length(tmp_res,1)
    loop
        update test4 set code='eda_'||code where code=tmp_res[i];
    end loop;

数组遍历与赋值

CREATE OR REPLACE FUNCTION func_test2() 
RETURNS INTEGER 
AS
$BODY$
DECLARE
    data int[] := array[1,2,3];
    i int;
BEGIN
    select array_agg(id) into data from test;
    raise notice 'func_name = % ','func_test';
    for i in 1..array_length(data,1) loop
            raise notice 'data[%] = % ',i,data[i];
    end loop;
    
    RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;